Mysql Select to find duplicates

Question

Can someone help me write sql select to complete the task. So the problem is that we have a table, and there are some duplicates, so I need to find where the name, street and house are the same and group them somehow.

I have almost this situation, but the difference is that I would like to group them in order to find what is duplicated.

Thanks in advance.

Answer 1

It is assumed that you have a field idthat will be grouped with a function GROUP_CONCAT()for each repeated line:

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

, :

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

t1.id < t2.id , .