Polarbehr Polarbehr - 3 months ago 9
SQL Question

Delete duplicate data in SQL preserving the oldest entry

I have a database that looks like the following

Col1 Col2
------------
0010 1111 (Delete this row)
0011 1112
0012 1111 (Keep this row)


I need to delete the duplicate data row found in Col2 based on Col1. I need to preserve the older entry and delete the younger. In this example I need to delete 0010 and keep 0012.

So far I have this code which shows me the duplicates in Col2 and shows the unique number from Col1

Select *
From [database]
Where (Col2) in (
Select Col2
From [database]
Group by Col2
Having Count(*) > 1
)


I'm not wrapping my head around what I need to do next to select the correct Col1 number so I can delete that row.

Answer
Declare @YourTable table (col1 varchar(25),col2 varchar(25))
Insert Into @YourTable values
('0010','1111'),
('0011','1112'),
('0012','1111')

;with cteBase as (
    Select *,RowNr=Row_Number() over (Partition By Col2 Order By Col1 Desc) from @YourTable
)
Select * From cteBase where RowNr>1
-- Delete From cteBase where RowNr>1
-- Remove Select if satisfied with results

Record(s) to be Deleted

col1    col2    RowNr
0010    1111    2
Comments