CASE Statement for Order By Clause with Multiple Columns and Desc/Asc Sort

Question

Following on from my earlier question here Case statement for Order By clause with Desc/Asc sort I have a statement like this:

SELECT 
    *
FROM
    TableName
WHERE
ORDER BY 
    CASE @OrderByColumn WHEN 1 THEN Forename END DESC, 
    CASE @OrderByColumn WHEN 2 THEN Surname END ASC

This works well, but sometimes I need more than column in the order by. I actually need something like this:

.....
ORDER BY
    CASE @OrderByColumn WHEN 1 THEN Forename, Date, Location END DESC

I can't work out how to make the CASE statement allow multiple columns in the THEN part.

GolezTrol · Answer 1 · 2019-12-20T14:50:50.673

You can write multiple cases, even if they all have the same condition.

ORDER BY 
    CASE @OrderByColumn WHEN 1 THEN Forename END DESC, 
    CASE @OrderByColumn WHEN 1 THEN Date END, 
    CASE @OrderByColumn WHEN 1 THEN Location END, 
    CASE @OrderByColumn WHEN 2 THEN Surname END ASC

Actually, you don't specify a column to sort by, but an expression.

The case statement returns null if the condition is not met, so actually it means:

CASE @OrderByColumn WHEN 1 THEN Forename ELSE NULL END

So if @OrderByColumn is not 1 then the statement returns always NULL. That doesn't exclude it from sorting, by the way, but it puts all those rows together in the result, making 'SurName' the decisive sorting within that group of rows.

by this form you can specify a column more than one time in each separate `@OrderByColumn ` case and best answer — ahmad molaie, Dec 20 '19 at 12:08

score 25 · Accepted Answer · answered Sep 25 '14 at 22:12

25

Do you need this?

ORDER BY
    CASE @OrderByColumn WHEN 1 THEN Forename END DESC, Date, Location,
    CASE @OrderByColumn WHEN 2 THEN Surname END ASC

answered Sep 25 '14 at 22:12

Tim Schmelter

450,073
74
686
939

10

Won't this sort by `Date` and `Location` regardless? – Broots Waymb Aug 24 '18 at 19:07
3

in this form you can't specify `Date` or `Location` in second case – ahmad molaie Dec 20 '19 at 12:09
1

This answer's text formatting is confusing, when @OrderByColumn is 2, it will sort by "NULL, Date, Location, Surname". – Zmey Oct 29 '21 at 01:53

score 0 · Answer 3 · answered Aug 27 '22 at 09:01

0

This is better if you want to give sort keyword as well

ORDER BY 
CASE WHEN @SortExpression = 'Id ASC' THEN Id END ASC,    
CASE WHEN @SortExpression = 'Id DESC' THEN Id END DESC,

answered Aug 27 '22 at 09:01

Ali Nm

21
8

CASE Statement for Order By Clause with Multiple Columns and Desc/Asc Sort

3 Answers3

Linked