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

SqlMysqlSql Order-By

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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)

Solution 2 - Sql

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.

Solution 3 - Sql

SELECT * FROM your_table ORDER BY 0.1/your_field;

Solution 4 - Sql

The following query should do the trick.

(SELECT * FROM table WHERE num!=0 ORDER BY num) UNION (SELECT * FROM table WHERE num=0)
Categories
Recommended Sql Solutions
Recommended Mysql Solutions
Recommended Sql Order-By Solutions

Previous Solution:

How to unit test your API?

Unit TestingApiCakephp

Next Solution:

Order of responses to MongoDB $in query?

Mongodb

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionRoland RabienView Question on Stackoverflow
Solution 1 - SqlDaniel VassalloView Answer on Stackoverflow
Solution 2 - SqlperfectioView Answer on Stackoverflow
Solution 3 - SqlJijesh CherraiView Answer on Stackoverflow
Solution 4 - SqlSRKXView Answer on Stackoverflow