How do I delete unique rows from a table with duplicate keys?
By using the ROW_NUMBER() ranking function, you could achieve that. Here's an example below. The top two queries are just for data verification. I'm a firm believer that you should see what you will be deleting (and what will remain) before actually deleting data.
(Verification) Rows that will be deleted
;with cte as
(
select
*,
row_num =
row_number() over (partition by road order by ID)
from dbo.YourTable
)
select *
from cte
where row_num > 1;
(Verification) Rows that will not be deleted
;with cte as
(
select
*,
row_num =
row_number() over (partition by road order by ID)
from dbo.YourTable
)
select *
from cte
where row_num = 1;
Data Delete
Note: the below query, when modified to fit your environment will actually delete data. Therefore, you should ensure that you really want to delete the data, and also have a backup of the data/database prior to performing any data modification.
;with cte as
(
select
*,
row_num =
row_number() over (partition by road order by ID)
from dbo.YourTable
)
delete from cte
where row_num > 1;