Deleting duplicate entry in SQL

Question

Would you please help me to solve this duplication entry issue?

I have columns in my table as shown below;

**Date_time**           **Pro**                **Color**
2016-10-17              Basketball               Red
2016-10-17              Basketball               Red
2016-10-18              Hockey                   Silver
2016-10-18              Hockey                   Silver

I need to delete duplicated entries and ending up having;

**Date_time**           **Pro**                **Color**
2016-10-17      Basketball               Red
2016-10-18      Hockey                   Silver

I used this code to get what I want however, I was not success on my attempting.

DELETE FROM Sport WHERE date_time NOT IN (SELECT MIN(date_time)
    FROM sport GROUP BY date_time,pro,color)

Thanks a lot in advance.

Tag your question with the database you are using. Also, explain what "not success" means. — Gordon Linoff, Oct 18 '16 at 14:45
Possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — wazelin, Oct 18 '16 at 14:49

Gordon Linoff · Answer 1 · 2016-10-19T00:03:52.780

0

Presumably, you intend something like this:

DELETE FROM Sport
    WHERE s.date_time > (SELECT MIN(s2.date_time)
                         FROM sport s2
                         WHERE s2.pro = sport.pro and s2.color = sport.color
                        );

In fact, if you have multiple duplicated entries, then the above only removes one of them. Try something like this instead:

DELETE FROM Sport
    WHERE s.date_time = (SELECT MAX(s2.date_time)
                         FROM sport s2
                         WHERE s2.pro = sport.pro and s2.color = sport.color
                        );

edited Oct 19 '16 at 00:03

answered Oct 18 '16 at 14:47

Gordon Linoff

1,242,037
58
646
786

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 's'. – Chelsea Oct 18 '16 at 14:55
Most databases accept that syntax. You really should tag the question with the database you are using. – Gordon Linoff Oct 19 '16 at 00:04

score 0 · Answer 2 · answered Oct 18 '16 at 14:51

WITH numbered AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY [dupe-column-list] ORDER BY [dupe-column-list]) AS _dupe_num 
    FROM [table-name] 
    WHERE 1=1 -- any where clause if required
)
DELETE FROM numbered WHERE _dupe_num > 1;

The above query will use ROW_NUMBER() to only keep the first appearance of each record. In your case the [dupe-column-list] should include all your columns.

Deleting duplicate entry in SQL

2 Answers2