Find all duplicates in a column

Submitted by:David Villa

Date added:12 June, 2014

Category:MySQL

MySQL code to find all duplicates in a database column(s)n.

Tags: find duplicates , check duplicates in column

Code Snippet:

-- Depending on your SQL syntax, this might work:

SELECT COUNT(*), colname FROM TABLE
GROUP BY colname HAVING COUNT(*) > 1

-- or similarly,

SELECT FIELD, COUNT(FIELD) AS NumOccurrences
FROM TABLE GROUP BY FIELD HAVING ( COUNT(FIELD) > 1 )

-- or if trying to find a duplicate combination
-- of multiple columns,

SELECT column1, column2, column3, COUNT(column3)
FROM TABLE GROUP BY column1, column2, column3
HAVING COUNT(column3) > 1
 
 

Comments