Find rows that repeat a value that should be unique, show all copies, and delete all but one — three variations of the same GROUP BY/ROW_NUMBER idea.

SQL

SELECT * FROM contacts ORDER BY id;

Output

id | name  | email
---+-------+---------------
1  | Asha  | asha@mail.com
2  | Ravi  | ravi@mail.com
3  | Asha  | ASHA@mail.com
4  | Meera | meera@mail.com
5  | Ravi  | ravi@mail.com
6  | Ravi  | ravi@mail.com

Which values are duplicated?

SQL

SELECT email, COUNT(*) AS copies
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1;

-- case-insensitive duplicates
SELECT lower(email) AS email, COUNT(*) AS copies, group_concat(id) AS ids
FROM contacts
GROUP BY lower(email)
HAVING COUNT(*) > 1
ORDER BY email;

Output

email         | copies
--------------+-------
ravi@mail.com | 3

email         | copies | ids
--------------+--------+------
asha@mail.com | 2      | 1,3
ravi@mail.com | 3      | 2,5,6

Show every duplicate row

SQL

SELECT id, name, email,
       COUNT(*) OVER (PARTITION BY email) AS copies
FROM contacts
ORDER BY email, id;

Output

id | name  | email          | copies
---+-------+----------------+-------
3  | Asha  | ASHA@mail.com  | 1
1  | Asha  | asha@mail.com  | 1
4  | Meera | meera@mail.com | 1
2  | Ravi  | ravi@mail.com  | 3
5  | Ravi  | ravi@mail.com  | 3
6  | Ravi  | ravi@mail.com  | 3

SQL

-- rows whose (name, email) combination appears more than once
SELECT c.*
FROM contacts c
JOIN (SELECT name, email FROM contacts GROUP BY name, email HAVING COUNT(*) > 1) d
  ON d.name = c.name AND d.email = c.email
ORDER BY c.id;

Output

id | name | email
---+------+--------------
2  | Ravi | ravi@mail.com
5  | Ravi | ravi@mail.com
6  | Ravi | ravi@mail.com

Delete duplicates, keep the lowest id

SQL

DELETE FROM contacts
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM contacts
    ) WHERE rn > 1
);

SELECT * FROM contacts ORDER BY id;

Output

id | name  | email
---+-------+---------------
1  | Asha  | asha@mail.com
2  | Ravi  | ravi@mail.com
3  | Asha  | ASHA@mail.com
4  | Meera | meera@mail.com

SQL

-- without window functions: keep MIN(id) of each group
DELETE FROM contacts
WHERE id NOT IN (SELECT MIN(id) FROM contacts GROUP BY email);

SELECT * FROM contacts ORDER BY id;

Output

id | name  | email
---+-------+---------------
1  | Asha  | asha@mail.com
2  | Ravi  | ravi@mail.com
3  | Asha  | ASHA@mail.com
4  | Meera | meera@mail.com

Contact 3 (ASHA@mail.com) survived both deletes: the comparison is case-sensitive. Partition/group by lower(email) when duplicates should be detected case-insensitively.

MySQL / SQL Server

-- MySQL: self-join delete
DELETE c1 FROM contacts c1
JOIN contacts c2 ON c2.email = c1.email AND c2.id < c1.id;

-- SQL Server: delete through a CTE
WITH d AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM contacts)
DELETE FROM d WHERE rn > 1;

How do you delete duplicate rows but keep one?