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

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

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

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

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

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Related