Delete oldest duplicate in SQL

How Can We Help?

You are here:
< Back

I’ve got a table that has rows that are unique except for one value in one column (let’s call it ‘Name’). Another column is ‘Date’ which is the date it was added to the database.

What I want to do is find the duplicate values in ‘Name’, and then delete the ones with the oldest dates in ‘Date’, leaving the most recent one.

https://stackoverflow.com/questions/679855/how-can-i-find-duplicate-entries-and-delete-the-oldest-ones-in-sql

create table #Product (
    ID      int identity(1, 1) primary key,
    Name        varchar(800),
    DateAdded   datetime default getdate()
)

insert  #Product(Name) select 'Chocolate'
insert  #Product(Name,DateAdded) select 'Candy', GETDATE() + 1
insert  #Product(Name,DateAdded) select 'Chocolate', GETDATE() + 5
select * from #Product

;with Ranked as (
    select  ID, 
        dense_rank() 
        over (partition by Name order by DateAdded desc) as DupeCount
    from    #Product P
)
delete  R
from    Ranked R
where   R.DupeCount > 1

select * from #Product