Finding Duplicates with SQL

Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

<cfquery name="test" datasource="datasource" username="username" password="password"> SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 ) </cfquery>

You could also use this technique to find rows that occur exactly once:

<cfquery name="test" datasource="datasource" username="username" password="password"> SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 ) </cfquery>

If you want to remove all duplicates in a table, try this:

<cfquery name="test" datasource="datasource" username="username" password="password"> Select distinct email FROM users GROUP BY users </cfquery>

SQL: DISTINCT Clause

The DISTINCT clause allows you to remove duplicates from the result set.  The DISTINCT clause can only be used with select statements.

Advertisement

No User Comments.

No User Comments, be the first one to write your comments?