Duplicate Entries


/ Published in: MySQL
Save to your folder(s)



Copy this code and paste it in your HTML
  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)

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.