Posted By

Abe on 09/24/10


Tagged

mysql duplicates


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

Jdub7
virendrakryadav


Duplicate Entries


 / Published in: MySQL
 

URL: http://stackoverflow.com/questions/3501265/mysql-select-to-find-duplicates

  1. SELECT t1.name, t1.street, t1.house, GROUP_CONCAT(DISTINCT t1.id) dupes
  2. FROM your_table t1
  3. JOIN your_table t2 ON (t2.name = t1.name AND
  4. t2.street = t1.street AND
  5. t2.house = t1.house)
  6. GROUP BY t1.name, t1.street, t1.house
  7. HAVING COUNT(*) > 1;
  8.  
  9. CREATE TABLE your_table (
  10. id INT,
  11. name VARCHAR(10),
  12. street VARCHAR(10),
  13. house VARCHAR(10)
  14. );
  15.  
  16. INSERT INTO your_table VALUES (1, 'a', 'b', 'c');
  17. INSERT INTO your_table VALUES (2, 'a', '1', 'c');
  18. INSERT INTO your_table VALUES (3, 'a', '2', '3');
  19. INSERT INTO your_table VALUES (4, 'a', 'b', 'c');
  20. INSERT INTO your_table VALUES (5, 'a', 'b', 'c');
  21. INSERT INTO your_table VALUES (6, 'c', 'd', 'e');
  22. INSERT INTO your_table VALUES (7, 'c', 'd', 'e');
  23.  
  24. +------+--------+-------+-------+
  25. | name | street | house | dupes |
  26. +------+--------+-------+-------+
  27. | a | b | c | 1,5,4 |
  28. | c | d | e | 6,7 |
  29. +------+--------+-------+-------+
  30. 2 rows IN SET (0.03 sec)

Report this snippet  

You need to login to post a comment.