the.blog

Home / Development / Find duplicate rows in a database using standard SQL



Find duplicate rows in a database using standard SQL

Posted by kinabalu on 31 Jan 2006 / 3 Comments


Thanks goes to Xgc in #mysql on freenode for showing me this little one-liner. We needed to add a unique key to one of our tables, and a duplicate was in our midst. Enter this handy one-liner:

?View Code SQL 
SELECT field1, count(*) FROM tbl1 GROUP BY field1 HAVING count(*) > 1;

problem solved, a nice simple list of the duplicated rows in front of you.

Share this:
  • Digg
  • DZone
  • Facebook
  • Technorati
  • LinkedIn
  • del.icio.us
  • Google Bookmarks
  • email
  • TwitThis



  • Pingback: Something is going on » Find duplicate rows in a database using standard SQL

  • Jacek

    That’s right. But if you wanted to do anything with the duplicates, for example to delete them, you would have to do another query to find the primary keys.

    Assuming that pk is the primary key I select the duplicates (without the record being duplicated) by a following query:

    select tbl1.* from tbl1 inner join tbl1 tbl1_ on tbl1.field1 = tbl1_.field1 where tbl1.pk > tbl1_.pk

    If I replace “select tbl1.” with “delete” I can also delete duplicates with just this one statement. So if there were 3 records with the same value of field1, after running the query only 1 will remain.

  • Pingback: Tagz | "mystic blog » Find duplicate rows in a database using standard SQL" | Comments

  • Speaking at Confoo

    I am speaking at ConFoo. February 25 - March 1, 2013 | Montreal, Canada