CODEWITHSUNDEEP
mysqlsql
luqita
luqita

Reputation: 4077

Selecting duplicates

I have a table that looks like this:

id| country  |
===================
1 | Argelia  | 
2 | USA      |
3 | China    |
4 | Italy    |
4 | Italy    |
6 | USA      |
7 | USA      |
1 | Argelia  |

I want a query that selects duplicate countries, but only when the ids are different. Thus, from the table above, the query would produce something like:

id| country  |
===================
2 | USA      |
6 | USA      |
7 | USA      |

Italy and Argelia are duplicate countries as well but since they shared an id, they shouldn't be in the output. What can I do?

Upvotes: 0

Views: 72

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

SELECT c.id, 
       c.country 
FROM   countries c 
       INNER JOIN (SELECT country, 
                          COUNT(DISTINCT id) k 
                   FROM   countries 
                   GROUP  BY country 
                   HAVING k > 1) t 
         ON c.country = t.country

see it working

Upvotes: 4

Marc B
Marc B

Reputation: 360672

SELECT country, COUNT(DISTINCT id) AS cnt, GROUP_CONCAT(id)
FROM yourtable
GROUP BY country, id
HAVING cnt > 1

Upvotes: 0

Related Questions