Question 183699 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – sql query to sort by multiple conditions

I would like to know how to sort values in mysql query.

I have a table tasks and has column cost which can have null or number values

other column status have 2703, 2702, 2701

Based on condition, the order by should be

-cost with value null and the status should be order 2701, 2702, 2703 always last

-cost with value numbers and status with only 2701, 2702 should be first

(i.e) cost with either null/number with status 2703 should be last.

Tried query

cost status
1    2703
null 2702
2    2701

Expected order should be

cost status
2    2701
null 2702
1    2703 // alwys status `2703` should be last after null if cost has either value/null



select * from tasks t1 where active = true 
ORDER BY t1.cost IS NULL, t1.cost asc, t1.statusId asc
Tags:

2

Answers


  1. 0 
    select * 
from tasks t1 
where t1.active = true and t1.cost is not NULL and t1.statusID in (2701, 2702) 
union all 
select * 
from tasks t2 
where t2.active = true and t2.cost is NULL

    Although re-reading your question it seems you just want NULL cost at the bottom. For that all you need is
    select * from tasks t1 where t1.active = true order by t1.cost, t1.statusID and null will go to the bottom

    Login or Signup to reply.
  2. 0

    Try something like:

    select * from tasks t1 where active = true 
ORDER BY 
CASE WHEN status = 2703 THEN 2 ELSE 1 END asc,
t1.cost IS NULL, t1.cost asc, t1.statusId asc
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search