Tagged: group-by

Find duplicate records in MySQL

- by admin

So, the task is to get duplicate records from a MySQL database.

The easy way:
SELECT COUNT(*), column1, column2 FROM tablename
GROUP BY column1, column2
HAVING COUNT(*)>1;

More complex case: shows each duplicated row:

It can be done using subquery:
SELECT firstname, lastname, list.address FROM list
INNER JOIN (SELECT address FROM list
GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.address

or with INNER JOIN:
SELECT a.firstname, a.lastname, a.address
FROM list a
INNER JOIN list b ON a.address = b.address
WHERE a.id <> b.id

If the same 'address' exist more than two times, then DISTINCT is needed.

« All tags