How to restrict a value based on status for a id with multiple records in SQL-CodePudding

ID	Status
1	Active
1	Inactive
2	Active
3	Inactive
4	Active
4	Inactive

In the above table when id has multiple status, need only the records with status as Active along with the remaining records.

ID	Status
1	Active
2	Active
3	Inactive
4	Active

CodePudding user response：

This is a "top 1 per group" problem I think, if I've interpreted your expected results correctly (I don't quite follow the question body).

; -- ensure prior statement is terminated; see sqlblog.org/cte
WITH src AS 
(
  SELECT ID, Status, rn = ROW_NUMBER() 
    OVER (PARTITION BY ID ORDER BY Status) 
  FROM dbo.table_name
)
SELECT ID, Status FROM src WHERE rn = 1;

It just so happens that Active sorts before Inactive but if you wanted to rely on something other than coincidental alphanumeric ordering, you could say:

OVER (PARTITION BY ID ORDER BY CASE Status
  WHEN 'Active'   THEN 1
  WHEN 'Inactive' THEN 2
  END
)

CodePudding user response：

You can also try, With an INDEX on [ID] and [Status] it would be quite fast

SELECT [ID],[Status] FROM tabl1 WHERE [Status] = 'Active'
UNION ALL
SELECT [ID],[Status] FROM tabl1 t1 WHERE NOT EXISTS(SELECT 1 FROM tabl1 WHERE [Status] = 'Active' AND [ID] = t1.[ID])

ID | Status  
-: | :-------
 1 | Active  
 2 | Active  
 4 | Active  
 3 | Inactive

db<>fiddle here