Mysql Select to find duplicates

Question

Hi all, can somebody help me to write a sql select to perform a task. So the problem is that we have a table and there are some duplicates, so i need to find where name, street and house are the same and to group somehow them.

I have almost this situation but the difference is that i would like to group them to find what is duplicate of what.

Thanks in advance.

Answer 1

This is assuming you have an id field, which will be grouped with the GROUP_CONCAT() function for each duplicate row:

SELECT    t1.name, t1.street, t1.house, GROUP_CONCAT(DISTINCT t1.id) dupes
FROM      your_table t1
JOIN      your_table t2 ON (t2.name = t1.name AND 
                            t2.street = t1.street AND 
                            t2.house = t1.house)
GROUP BY  t1.name, t1.street, t1.house
HAVING    COUNT(*) > 1;

Test case:

CREATE TABLE your_table (
   id int, 
   name varchar(10), 
   street varchar(10), 
   house varchar(10)
);

INSERT INTO your_table VALUES (1, 'a', 'b', 'c');
INSERT INTO your_table VALUES (2, 'a', '1', 'c');
INSERT INTO your_table VALUES (3, 'a', '2', '3');
INSERT INTO your_table VALUES (4, 'a', 'b', 'c');
INSERT INTO your_table VALUES (5, 'a', 'b', 'c');
INSERT INTO your_table VALUES (6, 'c', 'd', 'e');
INSERT INTO your_table VALUES (7, 'c', 'd', 'e');

Result:

+------+--------+-------+-------+
| name | street | house | dupes |
+------+--------+-------+-------+
| a    | b      | c     | 1,5,4 |
| c    | d      | e     | 6,7   |
+------+--------+-------+-------+
2 rows in set (0.03 sec)

Answer 2

To get duplicates, just use a self-join on the table :

select t1.id, t2.id, t1.name, t1.street, t1.house
from table t1
inner join table t2 on t1.name=t2.name and t1.street=t2.street and t1.house=t2.house
where t1.id < t2.id

The t1.id < t2.id will make sure every duplicate will only appear once.