sql - delete duplicates records leaving unique in group with priority -

i have table generated procedure cannot modify , returning data so:

user_id     active_street      street ----------- -----------        ----------------- 1           1                  street1 1           0                  street1 1           0                  other street 2           0                  other user street 2           0                  other user street 2           0                  other user street 2           1                  other user street 

i need remove records table following rules:

• every user has only one active street.
• i must delete duplicates removing have active_street set 0

so i'd leave these records:

user_id     active_street      street ----------- -----------        ----------------- 1           1                  street1 1           0                  other street 2           1                  other user street 

i've tried grouping there no id column can't id's delete.
how can delete duplicates without altering original table structure?

edit - based on gordon's answer
close, there litle difference:

if object_id( 'tempdb..#my_tmp' ) not null     begin         drop table #my_tmp;     end; select * #my_tmp   from(       select 1 user_id,              1 active_street,              'street1' street       union       select 2 user_id,              1 active,              'other user street' street       union       select 1 user_id,              0 active,              'street1' street       union       select 1 user_id,              0 active,              'other street' street       union       select 2 user_id,              0 active,              'other user street' street      union       select 2 user_id,              0 active,              'other user street 2' street ) x;   select *   #my_tmp order user_id, active_street desc;  select * ( select user_id, max(active_street) a, street #my_tmp group user_id, street ) x order user_id, desc   ;with todelete (       select row_number() on (partition user_id, active_street                                      order street) seqnum       #my_tmp t      ) delete todelete     seqnum > 1;      select *   #my_tmp order user_id, active_street desc; 

does want?

select user_id, active_street, min(street) street atable t group user_id, active_street; 

it returns results specify.

if want delete rows table, can use row_number():

with todelete (       select t.*, row_number() on (partition user_id, active_street                                      order street) seqnum       atable t      ) delete todelete     seqnum > 1; 

here sql fiddle demonstrates code.

edit:

ooops, think misunderstood logic. want delete streets same active street flag = 0. if so, query:

delete t my_tmp t     active_street = 0 ,           exists (select 1                   my_tmp t2                   t2.user_id = t.user_id ,                         t2.street = t.street ,                         t2.active_street = 1                  ); 

and here sql fiddle one.