Order by same as where in condtion

Question

How can i order records same as where I.ItemCode in () sequence

(sort by order of values in a select statement in clause)

Query

SELECT I.ItemCode,I.ItemName,UM.Name As Unit,
       SUM(CASE WHEN Rs.RestaurantID=1 THEN RD.Quantity ELSE Null END) [res1],
       SUM(CASE WHEN Rs.RestaurantID=2 THEN RD.Quantity ELSE Null END) [res2],

       SUM(RD.Quantity)TOTAL_QTY
FROM DBO.ITEMS I
LEFT JOIN UnitMeasure UM
ON I.UnitMeasureID=UM.UnitMeasureID
LEFT OUTER JOIN DBO.RequisitionDetails RD
ON I.ItemID=RD.ItemID
LEFT JOIN Requisitions R
ON RD.RequisitionID=R.RequisitionID
LEFT JOIN Restaurants Rs
ON R.RestaurantID=Rs.RestaurantID


where I.ItemCode in (355,365,360,275,335,350,395,320,310,340,345,305,325,315,388,300,383,385,250,245,453,326,366,368,375) and r.RequisitionDate='2016-09-23'


GROUP BY I.ItemCode,I.ItemName,UM.Name

Answer 1

You could also define a CTE with your ItemCodes and their order, then JOIN the CTE to your existing query. This will enable you to both filter rows on ItemCode and order rows on the sequence you want:

WITH ItemCodes AS (
    SELECT 0 AS [Order], 355 AS ItemCode UNION ALL
    SELECT 1 AS [Order], 365 AS ItemCode UNION ALL
    SELECT 2 AS [Order], 360 AS ItemCode
)

SELECT I.ItemCode,I.ItemName,UM.Name As Unit,
       SUM(CASE WHEN Rs.RestaurantID=1 THEN RD.Quantity ELSE Null END) [res1],
       SUM(CASE WHEN Rs.RestaurantID=2 THEN RD.Quantity ELSE Null END) [res2],

       SUM(RD.Quantity)TOTAL_QTY
FROM DBO.ITEMS I
JOIN ItemCodes 
ON I.ItemCode = ItemCodes.ItemCode
LEFT JOIN UnitMeasure UM
ON I.UnitMeasureID=UM.UnitMeasureID
LEFT OUTER JOIN DBO.RequisitionDetails RD
ON I.ItemID=RD.ItemID
LEFT JOIN Requisitions R
ON RD.RequisitionID=R.RequisitionID
LEFT JOIN Restaurants Rs
ON R.RestaurantID=Rs.RestaurantID


where r.RequisitionDate='2016-09-23'


GROUP BY I.ItemCode,I.ItemName,UM.Name

ORDER BY ItemCodes.[Order]

Answer 2

You need to explicitly hard code the ordering in Order by no other way

Order by Case ItemCode  when 355 then 0 
                        when 365 then 1 
                        when 360 then 2 
                        when 275 then 3
                        ..
                        when 368 then 24 
                        when 375 then 25 end asc

Each time you may have to build the Order by based on IN clause