How to delete the duplicate based on date

Question

I have a table where I have several cust_id duplicates. I would like to keep the row where prendate_next is nearest to the current date and delete the rest of the duplicates. Please help me how. I am new to this

cust_id     prendate_next
1000105737  2014-11-30 00:00:00.000
1000105836  2014-11-20 00:00:00.000
1000143646  2014-11-10 00:00:00.000
1000143646  2015-03-09 00:00:00.000
1000179487  2014-12-05 00:00:00.000
1000182253  2015-01-01 00:00:00.000
1000192740  2014-10-02 00:00:00.000
1000192740  2015-01-10 00:00:00.000
1000199419  2015-09-30 00:00:00.000
1000170578  2014-12-26 00:00:00.000
1000188890  2015-06-23 00:00:00.000
1000189075  2015-03-01 00:00:00.000
1000189075  2015-03-01 00:00:00.000
1000189144  2015-04-04 00:00:00.000

Do you have another unique id in this table?

juergen d
– juergen d

2014-10-08 15:19:20 +00:00
Commented Oct 8, 2014 at 15:19 — juergen d
– juergen d, Commented Oct 8, 2014 at 15:19
duplicate: stackoverflow.com/questions/18256201/…

Tab Alleman
– Tab Alleman

2014-10-08 15:20:30 +00:00
Commented Oct 8, 2014 at 15:20 — Tab Alleman
– Tab Alleman, Commented Oct 8, 2014 at 15:20
yes i do have an onther

Zeb
– Zeb

2014-10-08 15:23:41 +00:00
Commented Oct 8, 2014 at 15:23 — Zeb
– Zeb, Commented Oct 8, 2014 at 15:23

Code Different · Accepted Answer · 2014-10-08 16:07:49Z

1

;WITH cte AS (
    SELECT      cust_id, prendate_next,
                ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY ABS(DATEDIFF(DAY,prendate_next,GETDATE()))) AS RowNumber
    FROM        MyTable
)

DELETE MyTable
    FROM        MyTable
    INNER JOIN  cte         ON MyTable.cust_id = cte.cust_id
                            AND MyTable.prendate_next = cte.prendate_next
    WHERE       cte.RowNumber != 1

ABS(DATEDIFF(DAY,prendate_next,GETDATE())) counts how many days prendate_next is from today.

answered Oct 8, 2014 at 16:07

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

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

Collectives™ on Stack Overflow

How to delete the duplicate based on date

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related