Finding duplicates SQL

Question

Let me first give some example table table which will make my question easier to understand.

Column1      Column2      Column3
Valuea       Value123     Value456
Valueax      Value123     Value456
Valueb       Value123     Value456
Valueb       Value123     Value456

select   Column1,
     Column2,
         Column3,
         count(*)
from     ColumnName
group by 
     Column1,
     Column2,
     Column3,
having   count(Count2+Count3) > 1

I want to return 'column1', 'column2', 'column3' where column 2and3 will contain more than more duplicates but 'column1' must be different. So at present the above code would return:

Valueb       Value123     Value456 2

but i want it to return

Valuea       Value123     Value456
Valueax      Value123     Value456

where column1 has different values but column2 and column3 values are the same.

Aaron Bertrand · Accepted Answer · 2012-04-16 18:02:38Z

3

You can't group by concatenating. This will treat 'xxx' + 'yyy' the same as 'x' + 'xxyyy'. How about getting

;WITH x AS 
(
   SELECT Column2, Column3 
   FROM dbo.table GROUP BY Column2, Column3
   HAVING COUNT(*) > 1
)
SELECT t.Column1, t.Column2, t.Column3
FROM x INNER JOIN dbo.table AS t
ON x.Column2 = t.Column2
AND x.Column3 = t.Column3
GROUP BY t.Column1, t.Column2, t.Column3
HAVING COUNT(*) = 1;

This assumes that none of these columns are NULLable.

edited Apr 16, 2012 at 18:02

answered Apr 16, 2012 at 16:09

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

1 Comment

Aaron Bertrand Over a year ago

Note that if you have three rows and some are duplicates (e.g. Valuea, Valueax and Valueax) the latter two won't be reported... if these are potential cases you'll need to supply more sample data and how you want to handle those cases.

Turbot · Accepted Answer · 2012-04-16 18:34:28Z

0

If I understand your question correctly,

You want to have unique row record for your table. Instead of using Group By who Aaron mentioned you can't use in concatenating.

However , you can use Over to do that.

UPDATE : you need to consider what are you need to group together as Aaron mentioned "x" + "yy" is identical with "xy" + "y"

With Data AS
(
SELECT ROW_NUMBER() Over 
     (Partition By "Key" Order by "Key") as R,
     Column1, Column2, Column3
)
SELECT * FROM Data 
WHERE R = 1

edited Apr 16, 2012 at 18:34

answered Apr 16, 2012 at 17:49

Turbot

5,2511 gold badge24 silver badges31 bronze badges

3 Comments

Aaron Bertrand Over a year ago

Partition By Column1+Column2+Column3? That is rather dangerous. 'x' + 'yy' + 'z' will be treated the same as 'xy' + 'y' + 'z' but I don't consider those rows identical. In any case even if you ignore that I don't think this gives the desired result - why WHERE R = 1?

Turbot Over a year ago

I didn't notice that after read your post. I'll edit the answer.

Aaron Bertrand Over a year ago

Still don't see how this could possibly derive the desired result.

Collectives™ on Stack Overflow

Finding duplicates SQL

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related