Ergec Ergec - 5 months ago 11
MySQL Question

Getting and ordering rows with value greater than zero then rows with value zero

I have a table like this

id title display_order
1 t1 3
2 t2 1
3 t3 5
4 t4 4
5 t5 2
6 t6 0
7 t7 7
8 t8 6
9 t9 0
10 t10 0


What I need is to have results like this

id title display_order
2 t2 1
5 t5 2
1 t1 3
4 t4 4
3 t3 5
8 t8 6
7 t7 7
...order of the rest is not important but should be in the result
6 t6 0
9 t9 0
10 t10 0


I can get this result with two SQL queries and then combine them.

Is there a way to do this with one SQL?

Thanks

Answer
SELECT *
FROM atable
ORDER BY
  display_order = 0,
  display_order

When display_order is 0, the first sorting term, display_order = 0, evaluates to True, otherwise it evaluates to False. True sorts after False – so, the first sorting criterion makes sure that rows with the display_order of 0 are sorted at the end of the list.

The second ORDER BY term, display_order, additionally specifies the order for rows with the non-zero order values.

Thus, the two criteria give you the desired sorting order.

Comments