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
Bill
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
Mahmoud Gamal
source
to share
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
John Woo
source
to share
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
Bill
source
to share