SQL query help in removing duplicates

Don75 81 Reputation points
2022-02-10T16:30:03.347+00:00

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
Developer technologies Transact-SQL
SQL Server Other
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Cooper 8,481 Reputation points
    2022-02-10T17:16:09.147+00:00
    ;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

    0 comments No comments

  2. Naomi Nosonovsky 8,431 Reputation points
    2022-02-10T17:20:42.693+00:00

    ;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


  3. Don75 81 Reputation points
    2022-02-10T20:14:21.827+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.