Bill Bill - 1 year ago 105
SQL Question

TSQL Order By - List of hard-coded values

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 Source

For more status values, you can do this:

WITH StatusOrders
  SELECT StatusOrderID, StatusName
  FROM (VALUES(1, 'Active'), 
              (2, 'Deleted'),
              n, 'last status')) AS Statuses(StatusOrderID, StatusName)
FROM YourTable t
INNER JOIN StatusOrders s ON t.StatusName = s.StatusName
ORDER BY s.StatusOrderID;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download