Ayman Ayman - 1 year ago 64
SQL Question

Order by same as where in condtion

How can i order records same as

where I.ItemCode in ()

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


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],

LEFT JOIN UnitMeasure UM
ON I.UnitMeasureID=UM.UnitMeasureID
LEFT OUTER JOIN DBO.RequisitionDetails RD
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download