posted 21 years ago
Detecting duplicates
I'll show you two of the most useful SQL queries you'll ever learn. (Shh! Don't tell everybody!)
First assume a table with the following data. (No key fields.)
To find duplicates, use the following query.
To use this query in the more general case, just modify the list of fields in the GROUP BY clause (giving careful thought to key vs. nonkey fields and how you're defining a "duplicate").
To see the entire row for each of these "duplicates," just nest the above query:
For our present data, this query isn't particularly interesting because our table only has 1 field. It becomes more useful when you're dealing with rows that match on some fields but not others.
Deleting duplicates
Another way of "deleting" duplicates is to insert into a temp table, then rename the table.
Give careful thought to what fields are in the select (distinct) list because it will affect what's considered a duplicate.