CASE in ORDER BY few columns

Question

Using a case-then block, I need to choose how to order my SQL 2008 query, by [status] ASC, [date] DESC or just [date] DESC.

I know only how to use one column:

SELECT *
FROM table
ORDER BY
    CASE WHEN @flag = 0
     THEN R.[date] END DESC,
    CASE WHEN @flag = 1
     THEN R.[status] END ASC

How to use both columns in second CASE?

Answer 1

• If your flag is 0, then we'll order by (null, r.date desc).
• If not, we'll order by (r.Status, r.date desc)

Use the CASE expression to "project" a new value for ordering.

SELECT *
FROM table
ORDER BY
  CASE WHEN @flag = 0 THEN r.Status ELSE null END,
  r.[date] desc

Answer 2

Typically to put conditional ordering in, you just repeat the CASE statement.

SELECT *
FROM table
ORDER BY
    CASE WHEN @flag = 0 THEN R.[date] END DESC,
    CASE WHEN @flag = 0 THEN R.[somethingelse] END ASC,
    CASE WHEN @flag = 1 THEN R.[status] END ASC
;

But in your case, the overlap of 'date' means you can simplify it like David B's comment.