MySQL List All Duplicates

Question

Possible Duplicate:
Find duplicate records in MySQL

I have a table in MySQL like this:

ID    name    email
1    john     abc@abc.com
2    johnny   abc@abc.com
3    jim      eee@eee.com
4    Michael  abec@awwbc.com

How can I have the MySQL query that will list out the duplicate one like this?

Result of duplicate search:

ID    name    email         Duplicate
1    john     abc@abc.com      2
2    johnny   abc@abc.com      2

This exact question has already been answered: http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql — Eric Leschinski, Jan 21 '13 at 02:37

John Woo · Accepted Answer · 2013-01-21T02:47:14.990

35

SELECT  a.*, b.totalCount AS Duplicate
FROM    tablename a
        INNER JOIN
        (
            SELECT  email, COUNT(*) totalCount
            FROM    tableName
            GROUP   BY email
        ) b ON a.email = b.email
WHERE   b.totalCount >= 2

SQLFiddle Demo

for better performance, add an INDEX on column EMail.

OR

SELECT  a.*, b.totalCount AS Duplicate
FROM    tablename a
        INNER JOIN
        (
            SELECT  email, COUNT(*) totalCount
            FROM    tableName
            GROUP   BY email
            HAVING  COUNT(*) >= 2
        ) b ON a.email = b.email

SQLFiddle Demo

edited Jan 21 '13 at 02:47

answered Jan 21 '13 at 02:42

John Woo

258,903
69
498
492

@JW im doing something wrong, or you wrote too complicated query? See my answer. – Kamil Jan 21 '13 at 03:04
@Kamil the result of your query must have two records: `ID 1 and 2`. – John Woo Jan 21 '13 at 03:06
I guess you miss-understanding what I am asking here. There is only one table and the table below is the result from the mySQL query, not another table in the database. – user1995781 Jan 21 '13 at 03:18
@user1995781 the query involves only one table. – John Woo Jan 21 '13 at 03:19

score 3 · Answer 2 · answered Jan 21 '13 at 03:27

If you can live with having the ID and name in comma separated lists, then you can try:

select email, count(*) as numdups,
       group_concat(id order by id), group_concat(name order by id)
from t
group by email
having count(*) > 1

This saves a join, although the result is not in a relational format.

score 0 · Answer 3 · answered Jan 21 '13 at 02:42

0

Check this post on the MySQL forums, which gives the following:

SELECT t1.id, t1.name, t1.email FROM t1 INNER JOIN ( 
SELECT colA,colB,COUNT(*) FROM t1 GROUP BY colA,colB HAVING COUNT(*)>1) as t2 
ON t1.email = t2.email;

answered Jan 21 '13 at 02:42

hd1

33,938
5
80
91

MySQL List All Duplicates

3 Answers3

Linked