delete rows where id exists more than once but keep the record with the latest date stamp

Question

I have a table like below

 id     value      date
 abc     -0.4      2021-03-03
 def     0.2       2021-09-09
 abc     3.3       2000-01-01

I need to delete any ids that appear more than once but keep the record that has the latest date. So for eaxmple I want to run a query that would leave me a table like below,

 id     value      date
 abc     -0.4      2021-03-03
 def     0.2       2021-09-09

How do I write a delete query to delete records where the id exists more than once but keep the latest record?

Answer 1

A nice solution in SQL Server is an updatable CTE using a window function:

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