Page 1 of 1

Find Duplicate Entries

Posted: Mon Sep 18, 2006 8:40 am
by icesolid
Is there any SQL command I can run to search a MySQL DB and find any duplicate entries and then print out which ones are duplicate?

Posted: Mon Sep 18, 2006 9:20 am
by kaszu
Maybe there is a better solution, but this is how i would solve it for table where fields are "id" (UNIQUE), "title", "body"

Code: Select all

SELECT a.* FROM articles as a, articles as a2 where a.title = a2.title and a.body = a2.body and a.id <> a2.id

Posted: Mon Sep 18, 2006 9:53 am
by ody
Just use count() and group by the unique field and use the having clause to filter down to records with count >= 2.. somthing like this:

Code: Select all

select uniqueCol, count(*) as count from tbl_name group by uniqueCol having count >= 2

Posted: Mon Sep 18, 2006 11:59 am
by icesolid
Excellent!

Posted: Wed Sep 20, 2006 5:47 am
by dibyendrah
Just interested to know how to find duplicate record of more than one fields on the table using count function ..Does anybody knows that ? ;)

Posted: Wed Sep 20, 2006 8:44 am
by ody
Should be something like:

Code: Select all

select uniqueCol0, uniqueCol1, uniqueCol2, count(*) as count from tbl_name group by uniqueCol, uniqueCol, uniqueCol having count >= 2
[/quote]