How do you delete duplicate rows in a table and still maintain one copy of the duplicate?
There are a couple of ways of doing this and approaches vary based on how big your table is,
whether you have constraints in place, how programming intensive you want to go,
whether you have a surrogate key and whether or not you have the luxury of taking a table down. Approaches
vary from using subselects, dropping a table and rebuilding using a distinct query from temp table,
and using non-set based approaches such as cursors.
The approach we often use is this one:
WHERE someuniquekey NOT IN
FROM sometable As dup
GROUP BY dup.dupcolumn1, dup.dupcolumn2, dup.dupcolum3)