Jason92s Jason92s - 17 days ago 6
MySQL Question

Sort order in MySQL with 0, null, regular numbers

I have values as follows:

ID
0
NULL
NULL
2
0
3


Trying to get query sort order so it displays like this:

2
3
0
0
NULL
NULL


Any help is appreciated

Answer

You can use an IF statement to enforce your logic and then have the nulls at the bottom. Place a minus sign (-) before the column name to sort nulls.

Test case:

CREATE TABLE mytable (a int);

INSERT INTO mytable VALUES (NULL);
INSERT INTO mytable VALUES (NULL);
INSERT INTO mytable VALUES (NULL);
INSERT INTO mytable VALUES (0);
INSERT INTO mytable VALUES (0);
INSERT INTO mytable VALUES (0);
INSERT INTO mytable VALUES (1);
INSERT INTO mytable VALUES (2);
INSERT INTO mytable VALUES (3);
INSERT INTO mytable VALUES (4);
INSERT INTO mytable VALUES (5);

Result

SELECT *, IF (a = 0 OR a = NULL, NULL, a) as sort_order
FROM mytable
ORDER BY -sort_order DESC
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    0 |
|    0 |
|    0 |
| NULL |
| NULL |
| NULL |
+------+

http://sqlfiddle.com/#!9/58b399/14