MS SQL - find and delete duplicities

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?

enter image description here

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

You may have a look at [here](http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows/3822833#3822833) and many others. — bonsvr, Dec 09 '11 at 08:24
check this http://stackoverflow.com/questions/4510596/sql-query-to-delete-duplicate-rows-from-same-table/4510642#4510642 — Jahan Zinedine, Dec 09 '11 at 09:00

score 2 · Accepted Answer · answered Dec 09 '11 at 11:00

2

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.

answered Dec 09 '11 at 11:00

Gangnus

24,044
16
90
149

Yep, it's really nice solution! Thank you too, Gangnus – gaffcz Dec 09 '11 at 11:12

score 1 · Answer 2 · edited Dec 09 '11 at 08:44

1

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

edited Dec 09 '11 at 08:44

Igor Borisenko

3,806
3
34
49

answered Dec 09 '11 at 08:28

Ravaut123

2,764
31
46

This query deletes all records, but suppose to delete duplication, so if some values is in two rows then it should delete on row, not all rows, thanks for your time. – Elias Hossain Dec 09 '11 at 09:21
@Md. Elias Hossain - This is a select not a delete. Be carefull if you want delete the duplication and hold one row of the duplicated rows. Which one must stay in the table? I prefare to see the selection and then delete the rows manual – Ravaut123 Dec 09 '11 at 09:34
Thats fine, but the asker was looking for `DELETE` statement and I'm sure he'll not be able to find which row he should keep among duplicate rows, however the asker accepted your answer, thats okay according to him, thanks for your time. – Elias Hossain Dec 09 '11 at 09:38
Yes, i've updated it by `select B, C, D, MIN(X) as id` and `on ... and MyTable.id <> T2.id` and it works perfectly. Thank you :) (completly the same result as answer of Gangnus bellow) – gaffcz Dec 09 '11 at 11:18

CloudyMarble · Answer 3 · 2011-12-09T08:40:23.543

1

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)

edited Dec 09 '11 at 08:40

answered Dec 09 '11 at 08:29

CloudyMarble

36,908
70
97
130

score 1 · Answer 4 · answered Dec 09 '11 at 09:01

1

Please try with below query:

WITH CustomTable
 AS (SELECT  B, C, D, ROW_NUMBER() OVER (PARTITION BY B, C, D 
                                   ORDER BY ( SELECT 0)) RowNumber
     FROM   TableName)
DELETE FROM CustomTable
WHERE  RowNumber > 1

answered Dec 09 '11 at 09:01

Elias Hossain

4,410
1
19
33

MS SQL - find and delete duplicities

4 Answers4