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  lt  gt  0 THEN TblList PinRequestCount desc  TblList LastName ASC  TblList FirstName ASC  TblList MiddleName ASC END   CASE WHEN TblList HighCallAlertCount  lt  gt  0 THEN TblList HighCallAlertCount desc  TblList LastName ASC  TblList FirstName ASC  TblList MiddleName ASC END  Case WHEN TblList HighAlertCount  lt  gt  0 THEN TblList HighAlertCount DESC  TblList LastName ASC  TblList FirstName ASC  TblList MiddleName ASC END  CASE WHEN TblList MediumCallAlertCount  lt  gt  0 THEN TblList MediumCallAlertCount DESC  TblList LastName ASC  TblList FirstName ASC  TblList MiddleName ASC END  Case WHEN TblList MediumAlertCount  lt  gt  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  lt  gt  0 and TblList HighCallAlertCount  lt  gt  0 and TblList HighAlertCount  lt  gt  0  and TblList MediumCallAlertCount  lt  gt  0 and   TblList MediumAlertCount  lt  gt  0    at the same time

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  0 THEN TblList PinRequestCount END desc  CASE WHEN TblList HighCallAlertCount  lt  gt  0 THEN TblList HighCallAlertCount END desc   Case WHEN TblList HighAlertCount  lt  gt  0 THEN TblList HighAlertCount END DESC  CASE WHEN TblList MediumCallAlertCount  lt  gt  0 THEN TblList MediumCallAlertCount END DESC  Case WHEN TblList MediumAlertCount  lt  gt  0 THEN TblList MediumAlertCount END DESC  TblList LastName ASC  TblList FirstName ASC  TblList MiddleName ASC   Or possibly   ORDER BY  CASE    WHEN TblList PinRequestCount  lt  gt  0 THEN TblList PinRequestCount    WHEN TblList HighCallAlertCount  lt  gt  0 THEN TblList HighCallAlertCount    WHEN TblList HighAlertCount  lt  gt  0 THEN TblList HighAlertCount    WHEN TblList MediumCallAlertCount  lt  gt  0 THEN TblList MediumCallAlertCount    WHEN TblList MediumAlertCount  lt  gt  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  lt  gt  0 THEN 5    WHEN TblList HighCallAlertCount  lt  gt  0 THEN 4    WHEN TblList HighAlertCount  lt  gt  0 THEN 3    WHEN TblList MediumCallAlertCount  lt  gt  0 THEN 2    WHEN TblList MediumAlertCount  lt  gt  0 THEN 1 END desc  CASE    WHEN TblList PinRequestCount  lt  gt  0 THEN TblList PinRequestCount    WHEN TblList HighCallAlertCount  lt  gt  0 THEN TblList HighCallAlertCount    WHEN TblList HighAlertCount  lt  gt  0 THEN TblList HighAlertCount    WHEN TblList MediumCallAlertCount  lt  gt  0 THEN TblList MediumCallAlertCount    WHEN TblList MediumAlertCount  lt  gt  0 THEN TblList MediumAlertCount END desc  TblList LastName ASC  TblList FirstName ASC  TblList MiddleName ASC

[sql] CASE WHEN statement for ORDER BY clause

The answer is

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008-r2

Tags