Polarbehr Polarbehr - 1 year ago 78
SQL Question

SOLVED - 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. Any help would be greatly appreciated!

Thank You

Answer Source
Declare @YourTable table (col1 varchar(25),col2 varchar(25))
Insert Into @YourTable values

;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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download