September 17, 2012 - 5:19pm - 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.


---