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 &gt; 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 &gt; 1;