MS SQL - find and delete duplicities [duplicate]

Question

Possible Duplicate:
SQL - How can I remove duplicate rows?
SQL query to delete duplicate rows from same table?

How to find duplicity for example in this table?

Column A is unique ID and columns E and F are irrelevant, so rows 1,2,3 and rows 4,5 are duplicates

Answer 1

I have a more effective solution:

DELETE FROM MyTable 
    WHERE A NOT IN 
        (SELECT MIN(A) 
         FROM MyTable GROUP BY B, C, D
        );

Attention: this works if "A" is not NULL. So, for some similar tasks it won't help.

Answer 2

Try:

select count(A) Occurrence, B, C, D from TableName group by B, C, D having count(A) > 1

To get the IDs of the duplicated Columns use:

select A from TableName where (B + ' ' + C + ' ' + D) in (select B + ' ' + C + ' ' + D from TableName group by B, C, D having count(A) > 1)

Answer 3

select MyTable.A 
from MyTable 
     join (select B,C,D 
           from MyTable 
           group by B,C,D 
           having Count(*)>1) as T2 on MyTable.B = T2.B 
                                       and MyTable.C = T2.C 
                                       and MyTable.D = T2.D