advanced code snippet search

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

Expand | Embed | Plain Text 
  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.