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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Cooper 8,466 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 7,361 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.