MySQL List All Duplicates

duplicates, mysql, select, sql

Solution

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

Problem

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 ```

Original source

Related problems