Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
3 views
in SQL by (6.1k points)

I need 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 want to pull it so that it displays each row that is a duplicate. Something like:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

Any ideas on how this can be done? I'm attempting to avoid doing the first one then looking up the duplicates with a second query in the code.

1 Answer

0 votes
by (12.7k points)
edited by

The solution 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;

Are you looking for a good SQL tutorialRegister to this SQL online course fast and get certified.

Related questions

0 votes
1 answer
asked Jul 4, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...