Is it possible for SQL to find records with duplicates?

Question

Can I use a SQL query to find records where one field is identical in both? That is, can I use the following table and return 1,3 (the ids) by comparing the name columns (and ignoring the phone)?

    ID | Name | Phone

    1  | Bob  | 5555555555
    2  | John | 1234567890
    3  | Bob  | 1515151515
    4  | Tim  | 5555555555

score 10 · Accepted Answer · answered Jan 13 '10 at 00:36

10

To get all names that exist more than once you can execute this statement:

SELECT Name FROM People GROUP BY Name HAVING COUNT(*)>1;

answered Jan 13 '10 at 00:36

Joachim Sauer

302,674
57
556
614

1

To get the IDs you will probably have to use the above statement with another select statement. Something like: SELECT id FROM [Table Name] WHERE NAME IN (SELECT Name FROM [Table Name] GROUP BY Name HAVING COUNT(*) > 1) would work – Waleed Al-Balooshi Jan 13 '10 at 00:47
1

One thing I wanted to add is that you need to be careful when using Names as an identifier for deciding if a record is a duplicate or not, because unless you are sure that there will never be any two people with the same name in your table you will get into trouble. – Waleed Al-Balooshi Jan 13 '10 at 00:56

score 2 · Answer 2 · answered Jan 13 '10 at 00:44

2

To get the IDs of the duplicates "1,3" concatenated that way use GROUP_CONCAT:

SELECT GROUP_CONCAT( ID SEPARATOR ',' )
FROM Table
GROUP BY Name
HAVING COUNT(*) > 1

answered Jan 13 '10 at 00:44

martin clayton

76,436
32
213
198

score 0 · Answer 3 · answered Jan 13 '10 at 01:06

Another - not necessarily efficient - way to do this is with a self-join:

SELECT P1.Id, P2.Id
  FROM People P1, People P2
 WHERE P1.Id < P2.Id
   AND P1.Name = P2.Name;

The first condition ensures that you only see the pair (1,3) and not the extraneous pairs (3,1) or the identical rows (1,1), (3,3).

Is it possible for SQL to find records with duplicates?

3 Answers3

Linked