Removing duplicate rows (based on values from multiple columns) from SQL Server

Question

+--------------+------------+-------------------+------------+
| id           | DATA_ID    |   SourceID        | DateAdded  |
+--------------+------------+-------------------+------------+
|           1  | 304019032  | 1                 |  2018-01-20|
|           2  | 345583556  | 1                 |  2018-01-21|
|           3  | 299951717  | 3                 |  2018-01-23|
|           4  | 304019032  | 2                 |  2018-01-24|
|           5  | 298519282  | 2                 |  2018-01-24|
|           6  | 299951717  | 3                 |  2018-01-27|
|           7  | 345583556  | 1                 |  2018-01-27|
+--------------+------------+-------------------+------------+

I'm trying to delete duplicate rows that have the same DATA_ID and SourceID, leaving the row with the most recent date.

Please show which rows you would like to delete. I am guessing you just want to delete rows 1 and 3 based on your description. — Gordon Linoff
– Gordon Linoff, Commented Jan 27, 2018 at 23:01

Gordon Linoff · Accepted Answer · 2018-01-28 01:57:22Z

2

In SQL Server, I like to use row_number() and an updatable CTE for this:

with todelete as (
      select t.*, row_number() over (partition by sourceid, dataid order by dateadded desc) as seqnum
      from t
     )
delete from todelete
    where seqnum > 1;

This will work even when sourceid/dataid is NULL (which is probably not the case for you). It also does not assume that dateadded and id are mutually increasing (although that might be a reasonable assumption).

edited Jan 28, 2018 at 1:57

answered Jan 27, 2018 at 22:58

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JeffO Over a year ago

+1 - dateadded could get out of whack with ID if imported from another system and they wanted to retain the original date.

Alex Kudryashev Over a year ago

@Gordon Linoff do you mean delete from todelete where seqnum > 1?

Gordon Linoff Over a year ago

@AlexKudryashev . . . LOL. Yes. That would be the more appropriate answer.

juergen d · Accepted Answer · 2018-01-27 22:56:41Z

0

delete from your_table
where id not in 
(
  select max(id)
  from your_table
  group by data_id, sourceID
)

answered Jan 27, 2018 at 22:56

juergen d

205k40 gold badges303 silver badges377 bronze badges

Collectives™ on Stack Overflow

Removing duplicate rows (based on values from multiple columns) from SQL Server

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related