i have a User table which has many users but some users are having same first name and Last Name but only one user will have status active . So my requirement is if the user is unique i need the user regardless of Status but if the user is duplicate i need the record having status active. How can i achieve this in SQL server? Sorry For the confusion here is the example of User table
my result table should be
Here Steve Jordan is having 2 records so i need the record having status 1 and for records having distinct First name and last name i need all the records regard less of status.
Note : I have a user id as primary key but i am joining on first name and last name because other table doesn't have user id.