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

Question

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

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Oct 04 '21 at 10:31

score 0 · Accepted Answer · answered Oct 04 '21 at 07:12

0

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)

answered Oct 04 '21 at 07:12

George Menoutis

6,894
3
19
43

This will select inactive preferably. – Serg Oct 04 '21 at 07:14
While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. You can find more information on how to write good answers in the help center: https://stackoverflow.com/help/how-to-answer . Good luck – nima Oct 04 '21 at 09:11

score 0 · Answer 2 · answered Oct 04 '21 at 07:14

0

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. (if exists)

answered Oct 04 '21 at 07:14

BlackMath

1,708
1
11
14

This would still give me the duplicates with non-active status. – muddu83 Oct 04 '21 at 07:23

score 0 · Answer 3 · answered Oct 04 '21 at 08:43

0

Try this:

select top 1 with ties full_name "Full Name",Status,current_position "Current Position"
from employee
order by row_number() over(partition by full_name order by case Status when 'Active' then 0 else 1 end)

answered Oct 04 '21 at 08:43

Syed Md. Kamruzzaman

979
1
12
33

again, its the same query given by George in this thread. – muddu83 Oct 04 '21 at 08:47
Some modified. Please run this, you will get expected output. https://imgur.com/a/0UwG2X0 – Syed Md. Kamruzzaman Oct 04 '21 at 08:50
ok great. Thanks, George and Syed. There was a small mistake in my query. Now there is another issue for names with further initials like John A. Doe. Can we apply the above on two columns simultaneously like First Name and Last Name? – muddu83 Oct 04 '21 at 09:13

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

3 Answers3