CASE WHEN statement for ORDER BY clause

by

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

More Related Contents:

  1. Calculating distance between two points (Latitude, Longitude)
  2. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  3. T-SQL split string based on delimiter
  4. WHERE Clause vs ON when using JOIN
  5. Partition Function COUNT() OVER possible using DISTINCT
  6. CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]
  7. Cannot use UPDATE with OUTPUT clause when a trigger is on the table
  8. How to get last 7 days data from current datetime to last 7 days in sql server
  9. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  10. Reference an alias elsewhere in the SELECT list
  11. Using IF ELSE statement based on Count to execute different Insert statements
  12. How to find out what is locking my tables?
  13. Passing dynamic order by in stored procedure
  14. Select SQL Server database size
  15. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  16. How do I find the data directory for a SQL Server instance?
  17. How to return default value from SQL query
  18. How to concatenate all columns in a select with SQL Server
  19. How to automatically generate unique id in SQL like UID12345678?
  20. How to calculate age (in years) based on Date of Birth and getDate()
  21. How to get difference between two rows for a column field?
  22. SQL Server SELECT into existing table
  23. SQL Server: drop table cascade equivalent?
  24. Insert Update stored proc on SQL Server
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. Should I use the CASCADE DELETE rule? [duplicate]
  27. Dynamic order direction
  28. Why is RAND() not producing random numbers?
  29. SQL, How to Concatenate results?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment