question

leo2015 avatar image
leo2015 asked

Finding duplicates in a table

Hi, I want to find out all the duplicate records within the table, for exmaple, I have a table T and I wanted to find all the records having duplicate value in Column C. Thanks,
sql-server-2008sql-server-2005sql
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Something like SELECT * FROM T WHERE C IN (SELECT C FROM T GROUP BY C HAVING COUNT(*) > 1) Might work. Untested. Top of the head. And, indeed, it does. DECLARE @T TABLE ( id INTEGER IDENTITY , C VARCHAR(10) ); INSERT INTO @T VALUES ( 'foo' ), ( 'foo' ), ( 'bar' ); SELECT * FROM @T; SELECT * FROM @T WHERE C IN ( SELECT C FROM @T GROUP BY C HAVING COUNT(*) > 1 ); Tested in SQL2014. You weren't clear which version you were using, but the technique should be fine in SQL2005.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.