What is the query in MySQL to pull out the duplicate records

Question

Want to find duplicate records in a MySQL Database. This could be written with:

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

Which gives me:

101 Saint ST    3

I want to pull it so that it shows each row that is duplicate. example like:

SIM    JANE    101 Saint ST
JOHN   SMITH   101 Saint ST

Anyone have any idea how it could be done? I'm trying to avoid doing the first one then looking up the duplicates with a second query in the code.

It's already answered here: http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql — VikingBlooded, Jul 26 '16 at 13:57
@Mac23 How.. can it be the same exact question?? Are you a bot or something? — I.G. Pascual, Jul 26 '16 at 14:02

score 0 · Answer 1 · answered Jul 26 '16 at 13:57

One option here is to join your original table to a subquery which determines the counts:

SELECT t1.last, t1.first, t1.address
FROM list t1
INNER JOIN
(
    SELECT address
    FROM list
    GROUP BY address
    HAVING COUNT(*) > 1
) t2
    ON t1.address = t2.address

The subquery servers a filter which removes records not having at least one other record with a common address. Note that I removed the COUNT from the select list of this subquery, since you don't appear to be using it in your final result.

What is the query in MySQL to pull out the duplicate records

1 Answers1