0
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
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Mr Nothing
  • 1
  • 2
  • 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 Aug 03 '22 at 08:03

1 Answers1

0

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

nbk
  • 45,398
  • 8
  • 30
  • 47