How to filter duplicate rows based on a condition in tsql?-CodePudding

I have an employee table with duplicate records consisting of contract to hire or rebadge details.

FullName	Status	Current Position
John Doe	Inactive	Finance Analyst
John Doe	Active	Finance Manager
Mike Wood	Active	Project Manager
Mike Wood	Inactive	Consultant
Sam Hyke	Inactive	Director
Ahmed Saeed	Active	Supply Chain Manager

I would like to select all records irrespective of status and only in case of duplicates, it should filter and pick the rows with status as 'Active'.

Expected Outcome

FullName	Status	Current Position
John Doe	Active	Finance Manager
Mike Wood	Active	Project Manager
Sam Hyke	Inactive	Director
Ahmed Saeed	Active	Supply Chain Manager

CodePudding user response：

select top 1 with ties FullName,Status,[Current Position]
from yourtable
order by row_number() over(partition by FullName order by case Status when 'Active' then 1 else 0 end)

CodePudding user response：

You can select DISTINCT FullName, ordering the table by FullName, Status.

SELECT DISTINCT FullName,
       Status,       
       Current Position
FROM employees
ORDER BY FullName, Status;

After reordering, 'Active' will always be in first position.