snippetsqlMinor

How do I delete unique rows from a table with duplicate keys?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

uniquerowsdeletewithduplicatekeyshowfromtable

Problem

I have the following data:

road        |  ID  | other column  | other column | 
------------------+------+---------------+--------------+          
MAIN ST           |  1   |     ...       |     ...      |
MAIN ST           |  2   |     ...       |     ...      |        
MAIN ST           |  3   |     ...       |     ...      |
BERRY LN          |  4   |     ...       |     ...      |
BERRY LN          |  5   |     ...       |     ...      |
BERRY LN          |  6   |     ...       |     ...      |
JOHN RD           |  7   |     ...       |     ...      |
JOHN RD           |  8   |     ...       |     ...      |

I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:

road        |  ID  | other column  | other column | 
------------------+------+---------------+--------------+          
MAIN ST           |  3   |     ...       |     ...      |
BERRY LN          |  4   |     ...       |     ...      |
JOHN RD           |  8   |     ...       |     ...      |

where the id's are any value, as long as the road column is unique.

Is there a simple way to do this in SQL or T/SQL? I followed the guide here, but it only applies to duplicate keys with non-unique rows.

Solution

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;

Code Snippets

;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;

;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;

;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;

Context

StackExchange Database Administrators Q#62272, answer score: 8

Revisions (0)

No revisions yet.