Loading

Help need to Keep only specific value in a duplicated column

Help need to Keep only specific value in a duplicated column

Bineesh Viswanath

5y876

Hello Dear Friends,

I need your help to solve a issue in my sql part.

here is my table data;

ID Name Status

2 Aju Active

2 Aju Inactive

3 Siju Inactive

And my result is likely to be :

ID Name Status

2 Aju Active

3 Siju Inactive

In above table , i need to write a query to keep only row where status = Active

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka K S5y ago
Hi,
1. select top 1 with ties
2. id, name, Status
3. from tableName
4. order by row_number() over (partition by id order by status)
top 1 with ties returns the first row from each group. and Group by is done using order by row_number() over (partition by column name). Order by status will do order inside each group.
0
Sachin Singh5y ago
Test this
WITH CTE (Id, Name, Status, DuplicateCount)
AS
(
  SELECT Id, Name, Status,
  ROW_NUMBER() OVER(PARTITION BY Id, Name,
       Status ORDER BY Id) AS DuplicateCount
  FROM table where Status='Active'
) SELECT * from CTE Where DuplicateCount = 1
0