TSQL Order By - List of hard-coded values

Question

I have a query that returns among others a Record Status column. The record status column has several values like: "Active", "Deleted", etc ...

I need to order the results by "Active", then "Deleted", then etc ...

I am currently creating CTEs to bring each set of records then UNION ALL. Is there a better and dynamic way of getting the query done?

Thank you,

Answer 1

you can use CASE on here

ORDER BY CASE WHEN Status = 'Active' THEN 0 ELSE 1 END ASC

but if you have more values for status and you want to sort Active then DELETE

ORDER BY CASE WHEN Status = 'Active'  THEN 0 
              WHEN Status = 'Deleted' THEN 1
              ELSE 2 
         END ASC