JOE SKEET JOE SKEET - 2 months ago 12
SQL Question

Select statement to find duplicates on certain fields

Can you help me with SQL statements to find duplicates on multiple fields?

For example, in pseudo code:

select count(field1,field2,field3)
from table
where the combination of field1, field2, field3 occurs multiple times


and from the above statement if there are multiple occurrences I would like to select every record except the first one.

Answer

To get the list of fields for which there are multiple records, you can use..

select field1,field2,field3, count(*)
  from table_name
  group by field1,field2,field3
  having count(*) > 1

Check this link for more information on how to delete the rows.

http://support.microsoft.com/kb/139444

Edit : As the other users mentioned, there should be a criterion for deciding how you define "first rows" before you use the approach in the link above. Based on that you'll need to use an order by clause and a sub query if needed. If you can post some sample data, it would really help.