Delete Records Which Are Considered Duplicates Based On Same Value On A Column And Keep The Newest

I would like to delete records which are considered duplicates based on them having the same value in a certain column and keep one which is considered the newest based on Inserted

Solution 1:

Just as an academic exercise:

with cte as (
   select*, row_number() over (partitionby ID orderby InsertedDate desc) as rn
   from TestTable)
deletefrom cte
where rn <>1;

Most of the time the solution proposed by Sam performs much better.

Solution 2:

This works:

delete t 
from TestTable t
left join 
(
    select id, InsertedDate = max(InsertedDate) from TestTable
    groupby id
) assubonsub.id = t.id andsub.InsertedDate = t.InsertedDate
wheresub.id is null

If you have to deal with ties it gets a tiny bit trickier.