Clever Geek Handbook

TSQL Order By - List of hardcoded values

I have a query that returns among others the "Record Status" column. The record status column has several meanings, such as "Active", "Deleted", and so on.

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

I am currently creating a CTE to cast each recordset and then UNION ALL. Is there a better and dynamic way to get the request?

Thank,

+3


source to share


3 answers


For more status values, you can do this:



WITH StatusOrders
AS
(
  SELECT StatusOrderID, StatusName
  FROM (VALUES(1, 'Active'), 
              (2, 'Deleted'),
              ...
              n, 'last status')) AS Statuses(StatusOrderID, StatusName)
)
SELECT *
FROM YourTable t
INNER JOIN StatusOrders s ON t.StatusName = s.StatusName
ORDER BY s.StatusOrderID;
+5


source


you can use CASE

here

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


but if you have more values ​​for status

and want to sort Active

, thenDELETE 

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


source 


WITH
cteRiskStatus
AS
(
SELECT  RiskStatusID, RiskStatusName
FROM    (VALUES(1, 'Active'),
               (2, 'Draft'),
               (3, 'Occured'),
               (4, 'Escalated'),
               (5, 'Closed'),
               (6, 'Expired'),
               (7, 'Deleted')) AS RiskStatuses(RiskStatusID, RiskStatusName)
)
SELECT * FROM cteRiskStatus

thank

+1


source






More articles:


All Articles