T-SQL How To: Compare and List Duplicate Entries in a Table

Question

SQL Server 2000. Single table has a list of users that includes a unique user ID and a non-unique user name.

I want to search the table and list out any users that share the same non-unique user name. For example, my table looks like this:

ID User Name Name == ========= ==== 0 parker Peter Parker 1 parker Mary Jane Parker 2 heroman Joseph (Joey) Carter Jones 3 thehulk Bruce Banner

What I want to do is do a SELECT and have the result set be:

ID User Name Name == ========= ==== 0 parker Peter Parker 1 parker Mary Jane Parker

from my table.

I'm not a T-SQL guru. I can do the basic joins and such, but I'm thinking there must be an elegant way of doing this. Barring elegance, there must be ANY way of doing this.

I appreciate any methods that you can help me with on this topic. Thanks! ---Dan---

SQLMenace · Accepted Answer · 2010-06-10 13:08:02Z

One way

select t1.* from Table t1 join( select username from Table group by username having count(username) >1) t2 on t1.username = t2.username

user359040 · Accepted Answer · 2010-06-10 13:08:04Z

The simplest way I can think of to do this uses a sub-query:

select * from username un1 where exists (select null from username un2 where un1.user_name = un2.user_name and un1.id <> un2.id);

DVK · Accepted Answer · 2010-06-10 13:17:36Z

The sub-query selects all names that have >1 row with that name... outer query selects all the rows matching those IDs.

SELECT T.* FROM T , (SELECT Dupe_candidates.USERNAME FROM T AS Dupe_candidates GROUP BY Dupe_candidates.USERNAME HAVING count(*)>1 ) Dupes WHERE T.USERNAME=Dupes.USERNAME

ppolyzos · Accepted Answer · 2010-06-10 13:18:51Z

You can try the following:

SELECT * FROM dbo.Person as p1 WHERE (SELECT COUNT(*) FROM dbo.Person AS p2 WHERE p2.UserName = p1.UserName) > 1;

Collectives™ on Stack Overflow

T-SQL How To: Compare and List Duplicate Entries in a Table

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related