SQL - Finding rows with duplicate values in one column

Question

I have a table that includes these columns:

Nameplate
Model
Segment

A sample table looks like:

Nameplate  Model  Segment
Acura      ILX    Small Lux Car
Audi       Q5     Compact Lux Car
Audi       Q5     Mid Lux Car
Audi       SQ5    Compact Lux Car

I need to find all Nameplate, Model combinations with multiple Segments.

In the above table, I need it to return:

Audi       Q5     Compact Lux Car
Audi       Q5     Mid Lux Car

I thought the following would work:

SELECT DISTINCT 
   [Nameplate], [Model], [Segment]
FROM 
   dbo.[Weighted Extract]
GROUP BY 
   [Nameplate], [Model], [Segment]
HAVING 
   COUNT([Segment]) > 1;

The above code only returns combinations with multiple Nameplate, Model, and Segment rows. This should be easier than I am making it, but I'm stuck.

Tim Schmelter · Accepted Answer · 2014-02-17 15:19:35Z

4

You can use EXISTS:

SELECT [Nameplate], [Model], [Segment]
FROM dbo.[Weighted_Extract] we1
WHERE EXISTS
(
    SELECT 1 FROM dbo.[Weighted_Extract] we2
    WHERE we1.Nameplate = we2.Nameplate
    AND   we1.Model = we2.Model
    AND   we1.Segment <> we2.Segment
);

Demo

answered Feb 17, 2014 at 15:19

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

t-clausen.dk · Accepted Answer · 2014-02-17 15:40:27Z

1

Try this, this should work from sqlserver 2005+:

;WITH X AS
(
  SELECT [Nameplate], [Model], [Segment], 
         count(*) over (partition by [Nameplate], [Model]) cnt
  FROM dbo.[Weighted Extract]
)
SELECT [Nameplate], 
       [Model], 
       [Segment] 
FROM X 
WHERE cnt > 1

answered Feb 17, 2014 at 15:40

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Comments

Kaidjin · Accepted Answer · 2014-02-17 15:15:52Z

-1

Try this :

SELECT [Nameplate], [Model]
FROM dbo.[Weighted Extract]
GROUP BY [Nameplate], [Model]
HAVING COUNT(distinct [Segment]) > 1;

answered Feb 17, 2014 at 15:15

Kaidjin

1,5131 gold badge13 silver badges19 bronze badges

5 Comments

Martin Smith Over a year ago

Doesn't return Segment column as desired.

user3319605 Over a year ago

That was it! I was trying to add Segment to the SELECT statement and it was erroring. Thanks for the quick response.

user3319605 Over a year ago

@Martin - you are correct, but Kaidjin's code at least shows me where the duplicates are. Showing the Segment would be nice but was not critical for my needs at this point.

Martin Smith Over a year ago

So why tell us "I need it to return" a column that you don't actually need then? Or not accept one of the answers that answers the full question you asked. Including this "nice" aspect?

Kaidjin Over a year ago

The OP states "I need to find all Nameplate, Model combinations with multiple Segments". -> doesn't say he needs segments. Besides, how do you return the segment of a group with multiple segments ?

Collectives™ on Stack Overflow

SQL - Finding rows with duplicate values in one column

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related