Pedro Ordonez Pedro Ordonez - 4 months ago 8
MySQL Question

How to display 2 or more duplicate rows

i need to show 2 duplicate rows in a query where in

ID | Name | Address
--------------------
1 | John | 123 West Ave
2 | John | 123 West Ave
3 | Peter | 225 North
4 | Sally | 972 Kingsburg
5 | Peter | 225 North
6 | John | 772 Superman
7 | Peter | 882 Batman


and the result will be

ID | Name | Address
---------------------
1 | John | 123 West Ave
2 | John | 123 West Ave
3 | Peter | 225 North
5 | Peter | 225 North

Answer

If you want to show only records whose name and address come in pair duplicates, then you can try the following query:

SELECT t1.ID, t1.name, t1.address
FROM yourTable t1
INNER JOIN
(
    SELECT name, address
    FROM yourTable
    GROUP BY name, address
    HAVING COUNT(*) = 2
) t2
    ON t1.name = t2.name AND t1.address = t2.address

If you want any set of records which appear two or more times, then you can change the HAVING clause to HAVING COUNT(*) >= 2.