CODEWITHSUNDEEP
sqlsql-server
Surensiveaya
Surensiveaya

Reputation: 347

Conditional Order By in sql server with multiple order columns

i wish to perform conditional order by with multiple columns i am expecting a query like

order by 
case when price is null then name desc,age desc end
case when price is not null then price desc, name desc

can we achieve this.?

sample data 

id name age price
1  sam  12  100
2  marc 34  null
3  tom  45  null
4  tom  40  null

result i need is 

id name age price
1  sam  12  100   <--price is not null so sort by price desc
3  tom  45  null <--price is null so sort by name desc,age desc
4  tom  40  null <--price is null so sort by name desc,age desc
2  marc  34  null  <--price is null so sort by name desc,age desc

Upvotes: 1

Views: 1873

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Tim's answer is correct, but it can be simplified to:

order by (case when price is null then 1 else 2 end),
         price desc,
         name desc,
         age desc

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I think this is what you want:

ORDER BY
    CASE WHEN price IS NULL THEN name END DESC,
    CASE WHEN price IS NOT NULL THEN price END DESC,
    CASE WHEN price IS NULL THEN age END DESC,
    CASE WHEN price IS NOT NULL THEN name END DESC;

Appreciate that when price is NULL, the above reduces to:

ORDER BY
    name DESC,
    NULL DESC,
    age DESC,
    NULL DESC;

That is, the alternate CASE expressions just collapse to NULL, leaving the above equivalent to:

ORDER BY
    name DESC,
    age DESC;

Upvotes: 3

Related Questions