CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Dev
Dev

Reputation: 6710

CASE WHEN statement for ORDER BY clause

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.

Upvotes: 72

Views: 317226

Answers (5)

Sina Bagheri
Sina Bagheri

Reputation: 1

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;

Upvotes: 0

ledukilian
ledukilian

Reputation: 71

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

Upvotes: 3

sohan yadav
sohan yadav

Reputation: 69

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

Upvotes: -5

nagnath
nagnath

Reputation: 281

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

Upvotes: 22

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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

Upvotes: 106

Related Questions