JOE SKEET JOE SKEET - 16 days ago 6
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.

Comments