In my post yesterday, I shared a little known trick for sorting NULLs last when using ORDER BY ASC.
To summarize briefly, NULLs are treated as less than 0 when used in ORDER BY, However, sometimes you do not want that behavior, and you need the NULLs listed last, even though you want your numbers in ascending order.
So a query like the following returns the NULLs first (expected behavior):
SELECT * FROM t1 ORDER BY col1 ASC, col2 ASC; +--------+------+ | col1 | col2 | +--------+------+ | apple | NULL | | apple | 5 | | apple | 10 | | banana | NULL | | banana | 5 | | banana | 10 | +--------+------+
The trick I mentioned in my post is to rewrite the query like:
SELECT * FROM t1 ORDER BY col1 ASC, -col2 DESC;
The difference is that we added a minus sign (-) in front of the column …[Read more]