Case When Statement for Order by Clause

CASE WHEN statement for ORDER BY clause

CASE is an expression - it returns a single scalar value (per row). It can't return a complex part of the parse tree of something else, like an ORDER BY clause of a SELECT statement.

It looks like you just need:

ORDER BY 
CASE WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount END desc,
CASE WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount END desc, 
Case WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount END DESC,
CASE WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount END DESC,
Case WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount END DESC,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC

Or possibly:

ORDER BY 
CASE
   WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount
   WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount
   WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount
   WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount
   WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount
END desc,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC

It's a little tricky to tell which of the above (or something else) is what you're looking for because you've a) not explained what actual sort order you're trying to achieve, and b) not supplied any sample data and expected results, from which we could attempt to deduce the actual sort order you're trying to achieve.

---

This may be the answer we're looking for:

ORDER BY 
CASE
   WHEN TblList.PinRequestCount <> 0 THEN 5
   WHEN TblList.HighCallAlertCount <> 0 THEN 4
   WHEN TblList.HighAlertCount <> 0 THEN 3
   WHEN TblList.MediumCallAlertCount <> 0 THEN 2
   WHEN TblList.MediumAlertCount <> 0 THEN 1
END desc,
CASE
   WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount
   WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount
   WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount
   WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount
   WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount
END desc,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC

CASE statement in the ORDER BY CLAUSE

Just updated your query:

SELECT DISTINCT 
   oh.Order_Number AS Order_Number,
   oh.Status AS Order_Status,   
   oh.Customer_Name AS Customer_Name,
   vsc.Salesman_Name AS Salesman_Name,
   vsc.Email_Address AS Email_Address,
   od.Work_Code AS Work_Code,
   od.Product_Code AS Product_Code,
   CONVERT(char(10),od.Projected_Ship_Date,101) AS Projected_Ship_Date,
   CONVERT(char(10),od.Due_Date,101) AS OD_Due_Date,
   format(oh.Gross_Amount, '$#,##0.##') AS Gross_Amount,
   DATEDIFF(DAY,oh.Order_Date,'{%Current Date%}') AS DIP,
   od.Part_Number AS Part_Number,   
   od.Status AS Status,
   CAST(qd.Delivery_Notes AS NVARCHAR(MAX)) AS Delivery_Notes,
   CASE 
       WHEN od.Status = 'Firm' THEN 1      
       WHEN od.Status = 'In Process' THEN 2
       WHEN od.Status = 'Released' THEN 3
       ELSE 4
    END As StatusOrderId

FROM
    dbo.Order_Header oh LEFT OUTER JOIN dbo.Commission_Distribution cd ON 
    oh.Order_Header_ID = cd.Order_Header_ID LEFT OUTER JOIN 
    dbo.vSalesman_Code vsc ON cd.Salesman_Code = vsc.Salesman_Code JOIN
    dbo.Order_Detail od ON od.Order_Header_ID = oh.Order_Header_ID JOIN
    dbo.Quotation_Detail qd ON od.Quotation_Detail_ID = qd.Quotation_Detail_ID JOIN
    dbo.Quotation_Header qh ON qd.Quotation_Header_ID = qh.Quotation_Header_ID

WHERE

    oh.Status  =  'Open' AND
    cd.Company_Code  =  'AIN' AND
    oh.Customer_Name  NOT IN  ( 'A.I. Innovations' , 'AI PROPERTIES Fortville LLC' , 'AI-IN Intercompany' , 'AI-NC Intercompany' ) AND
    od.Status  <>  'Closed' AND
    LEFT(od.Part_Number, 3)  <>  'MTS' AND
    vsc.Salesman_Name  NOT IN  ( 'House' , 'House Accounts' ) AND
    od.Status  <>  'Hold' AND
    od.Product_Code NOT LIKE '%PROCES%' AND
    od.Product_Code NOT LIKE '%VISTA WARRANT%'

ORDER BY
   CASE 
       WHEN od.Status = 'Firm' THEN 1
       WHEN od.Status = 'In Process' THEN 2
       WHEN od.Status = 'Released' THEN 3
       ELSE 4
   END,
   vsc.Email_Address ASC, 
   CONVERT(char(10),od.Projected_Ship_Date,101) ASC

Case statement for Order By clause with Desc/Asc sort

You need to split your ORDER BY in two parts:

SELECT *
FROM
    TableName
WHERE
ORDER BY 
    (CASE @OrderByColumn
    WHEN 1 THEN Forename
    END) DESC -- Forename --> descending
,   (CASE @OrderByColumn
    WHEN 2 THEN Surname
    END) ASC -- Surname --> ascending

Why and How do ORDER BY CASE Queries Work in SQL Server?

How does this work?

ORDER BY (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END),
         col2;

Well, it works exactly as the code specifies. The first key for the ORDER BY takes on the values of 1 and 0 based on col2. The 1 is only when the value is NULL. Because 1 > 0, these are sorted after the non-NULL values. So, all non-NULL values are first and then all NULL values.

How are the non-NULL values sorted? That is where the second key comes in. They are ordered by col2.

Execution order of WHEN clauses in a CASE statement

The value that is returned will be the value of the THEN expression for the earliest WHEN clause (textually) that matches. That does mean that if your line 2 conditions are met, the result will be A2.

But, if your THEN expressions were more complex than just literal values, some of the work to evaluate those expressions may happen even when that expression is not required.

E.g.

 WHEN r.code= '00'                        then 'A1'
 WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'
 WHEN r.code ='0120'                      then 1/0
 WHEN r.code ='01'                        then 'A4'

could generate a division by zero error even if r.code isn't equal to 0120, and even if it's equal to 00, say. I don't know what the standard has to say on this particular issue but I know that it is true of some products.

Using case statement before order by clause

There is an AS [ProfileScore] too many in your ORDER BY. You cannot create aliases in ORDER BY.

If both Marks and Rank are numeric, I suggest:

SELECT TOP 1 P.[Score]
FROM dbo.[Profile] P
WHERE P.[ProfileId] = @ProfileId 
ORDER BY CASE WHEN P.[Marks] IS NULL THEN P.[Rank] ELSE -P.[Marks] END;

CASE Statement for Order By Clause with Multiple Columns and Desc/Asc Sort

Do you need this?

ORDER BY
    CASE @OrderByColumn WHEN 1 THEN Forename END DESC, Date, Location,
    CASE @OrderByColumn WHEN 2 THEN Surname END ASC 

Oracle sql order by with case statement

If you want the department name in descending order, then you have to include that information in the query:

ORDER BY (CASE DEPT_NAME
              WHEN 'ACCOUNT' THEN 1
              WHEN 'AUDIT' THEN 2
              WHEN 'FINANCE' THEN 3
              ELSE 4
          END) DESC,
         DEPT_NAME DESC;

There is no reason for the value of the CASE to be a character string. The logic really calls for a number. If you use strings, then values larger than 9 will not work as you expect them to.

---

Related Topics