;With cte As
(Select Unit, AccNo, Menu, RefDate, EnteredDate,
Row_Number() Over(Partition By Unit, AccNo, Menu, RefDate Order By EnteredDate Desc) As rn
From #temp)
Delete From cte Where rn > 1;
-- check result
Select * from #temp
Tom
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have a requirement to delete duplicate rows from my table. I am trying to simulate the issue using #temp table below.
As you can see, there are duplicate records for the same Unit, AccNo, Menu and RefDate. Only difference is the EnteredDateTime.
I need to keep the record that has the latest EnteredDate for each Unit, AccNo, Menu and RefDate.
Appreciate any help on this.
CREATE TABLE #temp(
[Unit] [varchar](30) NULL, [AccNo] [varchar](18) NULL, [Menu] varchar(10),[RefDate] datetime ,[EnteredDate] datetime,, [Desc] varchar(100)
)
Insert into #temp values('M111','AC111','AD1','2021-07-28 00:00:00.000','2021-08-26 14:49:10.000','aaa')
Insert into #temp values('M111','AC111','AD2','2021-07-29 00:00:00.000','2021-08-27 1:49:10.000','bbb')
Insert into #temp values('M111','AC111','AD3','2021-07-30 00:00:00.000','2021-08-27 14:49:10.000','ccc')
Insert into #temp values('M111','AC111','AD3','2021-07-30 00:00:00.000','2021-08-27 17:49:10.000','ddd')
Insert into #temp values('M111','AC111','AD4','2021-08-01 00:00:00.000','2021-08-27 17:49:10.000','eee')
Insert into #temp values('M111','AC111','AD4','2021-08-01 00:00:00.000','2021-08-27 17:55:10.000','fff')
Insert into #temp values('M111','AC111','AD4','2021-08-01 00:00:00.000','2021-08-28 17:55:10.000','ggg')
Insert into #temp values('M111','AC222','AD5','2021-08-02 00:00:00.000','2021-08-29 17:55:10.000','hhh')
Insert into #temp values('M222','AC333','AD1','2021-08-12 00:00:00.000','2021-09-01 05:55:10.000','III')
Insert into #temp values('M222','AC333','AD1','2021-08-12 00:00:00.000','2021-09-02 05:55:10.000','jjj')
Insert into #temp values('M222','AC333','AD1','2021-08-12 00:00:00.000','2021-09-02 07:55:10.000','kkk')
Insert into #temp values('M333','AC333','AD0','2021-08-12 00:00:00.000','2021-09-01 05:55:10.000','LLL')
Insert into #temp values('M333','AC333','AD1','2021-08-12 00:00:00.000','2021-09-01 15:55:10.000','mmm')
Insert into #temp values('M333','AC333','AD1','2021-08-12 00:00:00.000','2021-09-01 16:55:10.000','nnn')
Insert into #temp values('M333','AC333','AD1','2021-08-12 00:00:00.000','2021-09-01 17:00:10.000','ooo')
Insert into #temp values('M333','AC333','AD1','2021-08-12 00:00:00.000','2021-09-01 17:11:10.000','ppp')
Select * from #temp
drop table #temp
;With cte As
(Select Unit, AccNo, Menu, RefDate, EnteredDate,
Row_Number() Over(Partition By Unit, AccNo, Menu, RefDate Order By EnteredDate Desc) As rn
From #temp)
Delete From cte Where rn > 1;
-- check result
Select * from #temp
Tom
;with cte as (select *, row_number() over (partition by Unit, AccNo, Menu, RefDate order by EnteredDate DESC) as Rn
from #temp) -- assuming the real table has a PK column
delete from cte where Rn > 1
Thanks for the responses. Only issue is this doesn't work with the last 2 rows in my example.
Last 2 rows for ooo and ppp in the description field and I want to keep the record with description that has ppp.
But this code deletes that and keeps the one before.