CODEWITHSUNDEEP
sql
Oto Shavadze
Oto Shavadze

Reputation: 42793

Controll ASC / DESC sorting by CASE condition

with t(col) as(
    values
    (0),
    (1)
)
select * from t 
ORDER BY  
CASE WHEN 1=2 THEN col END ASC,
CASE WHEN 1=1 THEN col END DESC

Well, is this correct syntax for controlling ASC / DESC ordering by CASE expression?

Yes, this works and gives correct result's, though seems somehow not intuitive (for me at least), because ASC and DESC are outside of CASE..END block.

Probably sounds silly question, but, in this example, first condition is false, so what happens to ASC keyword? asking this because it is after the END of block, so little bit confused..

Upvotes: 0

Views: 340

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This is the correct syntax in general for controlling asc/desc. For specifically numerical values, you can use the negation trick:

order by (case when @desc = 1 then - col else col end),

In your example, the first case expression returns NULL. These are all equivalent for the purpose of ordering, so no order is imposed.

Why is the asc/desc outside the case? Because case is an expression in SQL that returns a scalar value. A scalar value is a single value, such as a date, string, or number (some databases expand this to include records, arrays, and/or tuples). The asc/desc is a syntactic element in the order by clause; it is not part of a scalar value.

Upvotes: 1

Related Questions