CASE WHEN statement for ORDER BY clause

Question

I am using SQL Server 2008 R2.

I want the priority based sorting for records in a table.

So that I am using CASE WHEN statement in ORDER BY clause. The ORDER BY clause is as below :

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

But it gives Incorrect syntax near the keyword 'desc'

Any solution?

Also I can have:

TblList.PinRequestCount <> 0 and TblList.HighCallAlertCount <> 0 and
TblList.HighAlertCount <> 0` and TblList.MediumCallAlertCount <> 0 and  
TblList.MediumAlertCount <> 0 

at the same time.

Corliss

can you show you full query? – Grath

U can write select * From TblList. – Corliss

i guess you have the case statement in your query right? – Grath

Can you show an example? – Myrticemyrtie

Answer 1

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

Ruffled

In this case the sequence is not getting maintained. I want the PinRequestCount<>0 records on the top with ascending sort order of FirstName, LastName and MiddleName. Then HighCallAlertCount records<>0 with ascending sort order count of FirstName, LastName and MiddleName. Then HighAlertCount <>0 with ascending sort order of FirstName, LastName and MiddleName and so on... – Corliss

@Corliss - will any rows ever have a situation where more than one of those Count columns is not equal to 0? – Ruffled

Also each of the columns for whom I am giving descending sort order can be NOT equal to zero at the same time. – Corliss

@Corliss - I've added a new guess - like I say, it would be easier if your question actually gave us the information on what you're trying to achieve. – Ruffled

Ok.. Let me give you some sample data with desired result. – Corliss

Sorry, I just was not aware of the Edit in answer but the comment. I think your last solution in working.. :) let me test it more. :) – Corliss

Answer 2

Another simple example from here..

SELECT * FROM dbo.Employee
ORDER BY 
 CASE WHEN Gender='Male' THEN EmployeeName END Desc,
 CASE WHEN Gender='Female' THEN Country END ASC

Caterinacatering

Dead link, Gender should be defined, what if the key comes from the backend, as in ${sort_by} = 'alphabetically' THEN name END ASC. This gives error in Psql, no such column. – Solanum

Answer 3

Maybe you can use the WITH clause :

WITH request AS (
    SELECT id
         , CASE
               WHEN a > 100 THEN 1
               ELSE 0
        END AS example
    FROM table
)
SELECT *
FROM request
ORDER BY example DESC

It also work with WHERE clause

Selfabnegation

Answer 4

Maybe you can make priority:

For example, we want show the year_of release that’s bigger than 2000:

SELECT name, year_of_release, other_sales, global_sales
FROM games
ORDER BY 
  CASE 
    WHEN year_of_release >= 2000 THEN 0 
    ELSE 1 
  END,



  #when year_of_release >= 2000 sort by global_sales otherwize sorting by 
  other_sales 
  CASE 
    WHEN year_of_release >= 2000 THEN global_sales 
    ELSE other_sales  
  END DESC,
  
#at the end ,if global_sales is equal with other_sales ,sorting by id ascending
  id ASC; 

Movie

Answer 5

declare @OrderByCmd  nvarchar(2000)
declare @OrderByName nvarchar(100)
declare @OrderByCity nvarchar(100)
set @OrderByName='Name'    
set @OrderByCity='city'
set @OrderByCmd= 'select * from customer Order By '+@OrderByName+','+@OrderByCity+''
EXECUTE sp_executesql @OrderByCmd 

Aloha

Welcome to Stack Overflow! I have edited your answer; by indenting the code by 4 spaces, it is put in code markdown. I suggest you add a little explanation to your answer, that you explain how this code solves the problem. You can edit the explanation into your answer. Good luck! – Cephalalgia