Ayman Ayman - 1 month ago 6
SQL Question

Order by same as where in condtion

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

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