Conditional Order By in sql server with multiple order columns

Question

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

Show us some sorted sample table data, to better describe what you want. — jarlh
– jarlh, Commented Jan 6, 2020 at 10:16

Tim Biegeleisen · Accepted Answer · 2020-01-06 10:21:35Z

3

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;

answered Jan 6, 2020 at 10:21

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-01-06 11:45:10Z

1

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

answered Jan 6, 2020 at 11:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Conditional Order By in sql server with multiple order columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related