Friday, September 27, 2019

sql - 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.


No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...