sql server - How to write a Sql query for retrieving one row from duplicate rows? -

i have user table has many users users having same first name , last name 1 user have status active . requirement if user unique need user regardless of status if user duplicate need record having status active. how can achieve in sql server? sorry confusion here example of user table

my result table should

here steve jordan having 2 records need record having status 1 , records having distinct first name , last name need records regard less of status.

note : have user id primary key joining on first name , last name because other table doesn't have user id.

select userid, firstname, lastname, status (     select *     , row_number() on (partition firstname, lastname                          order status desc) rownum     [user] ) u u.rownum = 1 

this groups first , last name, orders status active higher priority, , takes 1 of each unique first/last name combination. ensures each each unique first/last name combination in result set once, , if there multiples, active 1 one returned. if name combination has multiples, not active, 1 returned, chosen arbitrarily.