Getting and organizing rows with a value greater than zero, then rows with a value of 0

Question

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 

I need to have results like

 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 a single SQL?

thanks

Answer 1

 SELECT * FROM atable ORDER BY display_order = 0, display_order 

When display_order is 0, the first sort member display_order = 0 evaluates to True , otherwise it evaluates to False . True sorts after False - so the first sorting criterion ensures that rows with display_order of 0 are sorted at the end of the list.

The second term ORDER BY, display_order , additionally defines the order of rows with nonzero order values.

Thus, two criteria give you the desired sort order.

Answer 2

Try the following:

 SELECT * FROM table_name ORDER BY IF (display_order = 0, 9999999, display_order) 

Answer 3

Here is the solution in T-SQL, if someone needs it

 SELECT * FROM Table ORDER BY CASE WHEN display_order = 0 THEN display_order END ASC,CASE WHEN display_order > 0 THEN display_order END ASC