Get list of duplicate rows in MySql

Question

i have a table like this

ID     nachname     vorname
1       john         doe
2       john         doe
3       jim          doe
4       Michael     Knight

I need a query that will return all the fields (select *) from the records that have the same nachname and vorname (in this case, records 1 and 2). Can anyone help me with this? Thanks

Answer 1

The following query will give the list of duplicates :

SELECT n1.* FROM table n1
inner join table n2 on n2.vorname=n1.vorname and n2.nachname=n1.nachname
where n1.id <> n2.id

BTW The data you posted seems to be wrong "Doe" and "Knight" are a lastname, not a firstname :p.

Answer 2

The general solution to your problem is a query of the form

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

This will return one row for each set of duplicate row in the table. The last column in this result is the number of duplicates for the particular values.

---

If you really want the ID, try something like this:

SELECT id FROM 
t1, 
( SELECT col1, col2, count(*)
  FROM t1
  GROUP BY col1, col2
  HAVING count(*) > 1 ) as t2
WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2 

Haven't tested it though

Answer 3

You can do it with a self-join:

select distinct t1.id from t as t1 inner join t as t2 
on t1.col1=t2.col1 and t1.col2=t2.col2 and t1.id<>t2.id

the t1.id<>t2.id is necessary to avoid ids matching against themselves. (If you want only 1 row out of each set of duplicates, you can use t1.id<t2.id).