Find duplicate records in MySQL

Question

I want to pull out duplicate records in a MySQL Database. This can be done with:

SELECT address, count(id) as cnt FROM list GROUP BY address HAVING cnt > 1 

Which results in:

100 MAIN ST    2 

I would like to pull it so that it shows each row that is a duplicate. Something like:

JIM    JONES    100 MAIN ST JOHN   SMITH    100 MAIN ST 

Any thoughts on how this can be done? I'm trying to avoid doing the first one then looking up the duplicates with a second query in the code.

Answer 1

SELECT date FROM logs group by date having count(*) >= 2 

Answer 2

The key is to rewrite this query so that it can be used as a 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;