TSQL - Deleting a duplicate oldest value

Question

I would like to create a SQL script to do the following:

• Identify duplicate values in the USERID field
• Next delete the record based on the oldest LOGINTIM as shown below

Thank you

Answer 1

I like to use an updatable CTE for this:

with cte as (
    select row_number() over(partition by userid order by logintim desc) rn
    from mytable
)
delete from cte where rn > 1

For each userid, this retains the row with the most recent logintim and deletes the others (if any).