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.