CODEWITHSUNDEEP
sql-servert-sql
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

Order By With Multiple CASE Statements

I'm trying correct an error with an existing application (I do not have the source, this should really be handled in the UI). The gist of it has to be

1) if the OrderLineItem fk_OrderId = 888888888 It is the first line always.

2) if the Vendor Type starts with 'NEW%' it is always the next concurrent lines

3) anything else follows order is irrelevant

I'm playing with this, however I'm a little confused as to how the case by syntax works:

SELECT 
   [Columns]
FROM [dbo].[OrderLineItem] o
LEFT OUTER JOIN [OPUS2].dbo.Vendor v ON o.fk_VendorID = v.ID
WHERE fk_OrderID = @fk_OrderID OR fk_OrderID = 888888888
 ORDER BY 
 CASE WHEN o.ID = 888888888 THEN 1 END ASC,
 CASE WHEN v.[Type] Like 'NEW%' THEN 2 ELSE 3 END

Upvotes: 1

Views: 144

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You only need one CASE expression for this:

ORDER BY 
   CASE WHEN o.ID = 888888888 
          THEN 1
        WHEN v.[Type] LIKE 'NEW%' 
          THEN 2
        ELSE 3 
   END

Upvotes: 2

mwigdahl
mwigdahl

Reputation: 16578

This should work:

SELECT 
   [Columns]
FROM [dbo].[OrderLineItem] o
LEFT OUTER JOIN [OPUS2].dbo.Vendor v ON o.fk_VendorID = v.ID
WHERE fk_OrderID = @fk_OrderID OR fk_OrderID = 888888888
 ORDER BY 
 CASE WHEN o.ID = 888888888 THEN 1 ELSE 2 END ASC,
 CASE WHEN v.[Type] Like 'NEW%' THEN 1 ELSE 2 END ASC

This imposes two sort orders on your result. The first one will force the 888888888 value to the top, leaving everything else unsorted underneath it. The second sort will, within each distinct value in the outer sort (888888888 and non-888888888), sort NEW% values to the top and everything else to the bottom.

Upvotes: 1

Related Questions