selecting all duplicates

How can I update this to select all duplicates?

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

Currently, I think it just returs the addresses which are du开发者_运维技巧plciated. I want all duplicates.

---

Select * from list
where address in (
  select address from list group by address
  having count(*) > 1);

Look at this sample query I ran:

mysql> select * from flights;
+--------+-------------+
| source | destination |
+--------+-------------+
|      1 |           2 |
|      3 |           4 |
|      5 |           6 |
|      6 |           1 |
|      2 |           4 |
|      1 |           3 |
|      5 |           2 |
|      6 |           3 |
|      6 |           5 |
|      6 |           4 |
+--------+-------------+
10 rows in set (0.00 sec)

mysql> select * from flights where source in 
       (select source from flights group by source having count(*) > 1);
+--------+-------------+
| source | destination |
+--------+-------------+
|      1 |           2 |
|      5 |           6 |
|      6 |           1 |
|      1 |           3 |
|      5 |           2 |
|      6 |           3 |
|      6 |           5 |
|      6 |           4 |
+--------+-------------+
8 rows in set (0.00 sec)

---

If I'm correct, you're looking for the actual rows that contain duplicates -- so that if you have three rows with the same address, you return all three rows.

Here's how to do it:

SELECT * FROM list
WHERE address in (
    SELECT address FROM list GROUP BY address HAVING count(id) > 1
);

This should generally work unless your address is a 'text' field or if your address table has more than a few thousand duplicates.

---

Are you looking for this?

SELECT * FROM list
WHERE id IN (
    SELECT id FROM list
    GROUP BY address HAVING count(id) > 1
);