Saturday, 23 June 2007

Checking for Duplicates

Here is a quick way to check for duplicate records in the database, by using the 'group by' and 'having' sql commands.

The following example uses the trusty 'EMP' table and checks for duplicates in the 'JOB' column.
select   job,
sum(1) Duplicates
from emp
group by job
having sum(1) > 1
order by sum(1) desc

Or generically this query could be expressed:
select   [duplicate_column_name],
sum(1) Duplicates
from [table_name]
group by [duplicate_column_name]
having sum(1) > 1
order by sum(1) desc

Hope it helps!
Anthony.

No comments: