在mysql中查找重复记录

我想在MySQL数据库中提取重复的记录。这可以通过以下方法完成:

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;

结果是:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

我想把它拉出来,让它显示每一行是重复的。类似于:

JIM    JONES    100 MAIN ST

如果结果是:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST
JOHN   SMITH    100 MAIN ST

我想把它拉出来,让它显示每一行是重复的。类似于:

JOHN   SMITH    100 MAIN ST
JOHN   SMITH    100 MAIN ST

我只想要重复的数据而不是原始数据