Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 8 down vote accepted

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;
share|improve this answer
    
Nice, very fancy. So this groups each road into a partition, ranks them and deletes everything after the first ranked one? –  Conor Apr 1 at 21:18
    
Essentially, yes. That's basically what is happening. –  Thomas Stringer Apr 1 at 23:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.