Using MySql, can I sort a column but have 0 come last?

Question

I want to sort by an column of ints ascending, but I want 0 to come last. Is there anyway to do this in MySql?

Answer 1

You may want to try the following:

SELECT * FROM your_table ORDER BY your_field = 0, your_field; 

Test case:

CREATE TABLE list (a int);  INSERT INTO list VALUES (0); INSERT INTO list VALUES (0); INSERT INTO list VALUES (0); INSERT INTO list VALUES (1); INSERT INTO list VALUES (2); INSERT INTO list VALUES (3); INSERT INTO list VALUES (4); INSERT INTO list VALUES (5); 

Result:

SELECT * FROM list ORDER BY a = 0, a;  +------+ | a    | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | |    0 | |    0 | |    0 | +------+ 8 rows in set (0.00 sec) 

Answer 2

You can do the following:

SELECT value, IF (value = 0, NULL, value) as sort_order FROM table ORDER BY sort_order DESC 

Null values will be down of the list.