Stored Procedure with composite Order by-CodePudding

I am creating a stored procedure like this :

select * 
from INVENTORYTABLE inv
join INVENTORYTABLE_New invNew on inv.Stock_ID = invNew.Stock_ID
where 
   (
   (AvailabilityStatus ='A' Or AvailabilityStatus = 'B' Or AvailabilityStatus = 'D' Or AvailabilityStatus = 'H' or AvailabilityStatus='E' ) And 
   MediaCount>0
   )

I want to sort the results by stockId for Availability status 'A' to 'H' and then for 'E'. Is this possible?

What I want is to show results from A to H ordered by StockId and then E below the ordered by stock Id.

CodePudding user response：

You may implement an ORDER BY clause using a CASE expression:

ORDER BY 
   CASE WHEN AvailabilityStatus = 'E' THEN 1 ELSE 0 END, 
   stockId

CodePudding user response：

select * 
from INVENTORYTABLE inv
join INVENTORYTABLE_New invNew on inv.Stock_ID = invNew.Stock_ID
where 
 (
   (AvailabilityStatus ='A' Or AvailabilityStatus = 'B' Or AvailabilityStatus = 'D'    Or AvailabilityStatus = 'H' or AvailabilityStatus='E' ) And 
  MediaCount>0
 )
order by 
 case
   when AvailabilityStatus ='A' then 1
   when AvailabilityStatus ='B' then 2
   when AvailabilityStatus ='D' then 3
   when AvailabilityStatus = 'H' then 4
   when AvailabilityStatus='E' then 5
   else 100
 end

You can try something like this