How to display 2 or more duplicate rows

Question

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

you means want to show only those records which are duplicate? — Hamza Zafeer, Aug 03 '16 at 14:45
Possible duplicate of [mysql select ONLY duplicate records from database](http://stackoverflow.com/questions/9540996/mysql-select-only-duplicate-records-from-database) — Mike Donkers, Aug 03 '16 at 14:45
Question is now and after answers were given; are you getting "any" output at all? If something is failing here, we don't know about it. The question's a bit short on detail. — Funk Forty Niner, Aug 03 '16 at 14:56
Tim's answer seems to solve this. Did you try it? Plus, I placed a comment under one of the answers below. — Funk Forty Niner, Aug 03 '16 at 15:01
@Fred-ii- yes sir Tim's answer really solved my problem, thank you :) — Peter Ordonez, Aug 03 '16 at 15:03

score 3 · Accepted Answer · answered Aug 03 '16 at 14:45

3

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.

answered Aug 03 '16 at 14:45

Tim Biegeleisen

502,043
27
286
360

thank you sir. it totally worked!. i've been searching for this for 4hrs now. thank you!! – Peter Ordonez Aug 03 '16 at 15:01

score 2 · Answer 2 · answered Aug 03 '16 at 14:47

2

With aggregate functions?

SELECT ID, Name, Address
FROM le_table
GROUP BY Name, Address
HAVING COUNT(ID) > 1
ORDER BY ID

answered Aug 03 '16 at 14:47

Ayell

560
2
12

i tried using your query command, it doesn't show 2 duplicate rows like what i illustrated above. :( – Peter Ordonez Aug 03 '16 at 14:54
`HAVING COUNT(ID) > 1` given the added info, should (probably) read as `HAVING COUNT(Name) >= 2` and/or using the `Address` column. – Funk Forty Niner Aug 03 '16 at 15:00

score 0 · Answer 3 · answered Aug 03 '16 at 14:48

0

SELECT *
FROM table
GROUP BY Name, Address
HAVING COUNT(ID) > 1
ORDER BY ID

answered Aug 03 '16 at 14:48

Hamza Zafeer

2,360
13
30
42

How to display 2 or more duplicate rows

3 Answers3