In this article, I will explain a scenario where the user has to remove duplicate records with different statuses.
Scenario
Consider below the table with 2 columns (Employee Name and Status)
Now, write a query to fetch a unique Employee Name like this –
If the Employee Name is unique then select it and if there are 2 Employee Names (one is active and another one is inactive) then select the name which is active. Read the Remarks column below.
| Employee Name | Status | Remarks |
| Amar | 1 | Select this record |
| Amar | 0 | Remove this record because the same name is inactive status |
| Jack | 0 | Select this record as this is the only record |
| Saga | 1 | Select this record as this is the only record with active status |
| Kaka | 1 | Select this record as this is the only record with active status |
| Tata | 1 | Select this record as this is the only record with active status |
The solution to remove duplicate records with different status
Let’s write an SQL query to achieve the above output.
Create a temp table using the below script –
declare @tmpTable as table
(
EmployeeName nvarchar(100),
EmployeeCode nvarchar(100),
Status bit
)Insert some rows in the table –
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Amar','1002',1)
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Amar','1001',0)
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Saga','1003',1)
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Kaka','1004',1)
insert into @tmpTable (EmployeeName, EmployeeCode, Status) values ('Tata','1005',0)Your select query will give the below result –
| EmployeeName | EmployeeCode | Status |
| Amar | 1002 | 1 |
| Amar | 1001 | 0 |
| Saga | 1003 | 1 |
| Kaka | 1004 | 1 |
| Tata | 1005 | 0 |
However, we want the below output –
| EmployeeName | EmployeeCode | Status |
| Amar | 1002 | 1 |
| Saga | 1003 | 1 |
| Kaka | 1004 | 1 |
| Tata | 1005 | 0 |
Use the below SQL query to get the above output –
delete from @tmpTable
where EmployeeName in
(select EmployeeName from @tmpTable group by EmployeeName having count(*) > 1)
and Status = 0Run the above query and you will get desired output –
Hope you like this article.
If it is useful to you, please share this on social media platforms.
You may read this – SQL Server Interview Questions and Answers for beginners
