Find duplicate records in MySQL

mysql duplicates
Chris Bartow picture Chris Bartow · May 12, 2009 · Viewed 785.2k times · Source

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

Powerlord picture Powerlord · May 12, 2009

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;

Related questions

Delete all Duplicate Rows except for One in MySQL?

How would I delete all duplicate data from a MySQL Table? For example, with the following data: SELECT * FROM names; +----+--------+ | id | name | +----+--------+ | 1 | google | | 2 | yahoo | | 3 | msn | | 4 | google | | 5 | google | | 6 | yahoo | +----+--------+ I would use SELECT DISTINCT name …

Read More
Remove duplicate rows in MySQL

I have a table with the following fields: id (Unique) url (Unique) title company site_id Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL …

Read More
In MySQL, can I copy one row to insert into the same table?

insert into table select * from table where primarykey=1 I just want to copy one row to insert into the same table (i.e., I want to duplicate an existing row in the table) but I want to do this without …

Read More