finding duplicate in a table with a condition

Question

I have a query such as this:

select a.id, a.color, a.shade from colors a where a.color = 'red'

which fetches

ID | Color | shade -------|------------|--------- 23 |red | dark10 525 |red | light-10

Question:

How can I find all records in table colors where there are multiple (two) occurrences of samecolor AND their corresponding shades are NOT identical

e.g. records like the following will not be considered

ID | Color | shade -------|------------|--------- 23 |green | light-10 324 |green | light-10

Omnipresent · Accepted Answer · 2011-12-27 07:20:48Z

You can do this by utilizing a GROUP BY clause with a HAVING COUNT(*) = 1 condition:

declare @colors table ( id int, color nvarchar(100), shade nvarchar(100) ) insert into @colors select 23, 'red', 'dark10' union all select 525, 'red', 'light-10' union all select 23, 'green', 'light-10' union all select 324, 'green', 'light-10' select c.* from @colors c inner join ( select color, shade from @colors group by color, shade having COUNT(*) > 1 ) x on x.color = c.color and x.shade <> c.shade

I've corrected your answer relative to the question. I'll accept your answer. thanks
Thanks - I didn't have a ton of data to test on, so I appreciate you updating it.

Collectives™ on Stack Overflow

finding duplicate in a table with a condition

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related