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

This also will show you how many duplicates have and will order the results without joins

SELECT  `Language` , id, COUNT( id ) AS how_many
FROM  `languages` 
GROUP BY  `Language` 
HAVING how_many >=2
ORDER BY how_many DESC

Answer 2

select address from list where address = any (select address from (select address, count(id) cnt from list group by address having cnt > 1 ) as t1) order by address

the inner sub-query returns rows with duplicate address then the outer sub-query returns the address column for address with duplicates. the outer sub-query must return only one column because it used as operand for the operator '= any'

Answer 3

select `cityname` from `codcities` group by `cityname` having count(*)>=2

This is the similar query you have asked for and its 200% working and easy too. Enjoy!!!

Answer 4

Find duplicate users by email address with this query...

SELECT users.name, users.uid, users.mail, from_unixtime(created)
FROM users
INNER JOIN (
  SELECT mail
  FROM users
  GROUP BY mail
  HAVING count(mail) > 1
) dupes ON users.mail = dupes.mail
ORDER BY users.mail;

Answer 5

Not going to be very efficient, but it should work:

SELECT *
FROM list AS outer
WHERE (SELECT COUNT(*)
        FROM list AS inner
        WHERE inner.address = outer.address) > 1;

Answer 6

select * from table_name t1 inner join (select distinct <attribute list> from table_name as temp)t2 where t1.attribute_name = t2.attribute_name

For your table it would be something like

select * from list l1 inner join (select distinct address from list as list2)l2 where l1.address=l2.address

This query will give you all the distinct address entries in your list table... I am not sure how this will work if you have any primary key values for name, etc..