Find duplicate records in MySQL

Solution for :I want to pull out duplicate records in a MySQL Database.

The key is to rewrite this query so that it can be used as a sub-query.

EXAMPLE QUERY:

?

1 2 3 4 5 6 7 8 9

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;