Snippet Name: Find all duplicates in a column
Description: Easy ways to find all duplicates in a database column(s)n.
Comment: (none)
Language: MYSQL
Highlight Mode: MYSQL
Last Modified: March 01st, 2009
|
-- 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 |