ColdFusion Tutorial
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. |
ColdFusion Tutorial test
Finding Duplicates with SQL
No test is available or implemented for this ColdFusion Tutorial.
No User Comments.
No User Comments, be the first one to write your comments?
