Sorting Query Results - MySQL - page 264

Suppose that a column contains NULL values:
mysql> SELECT val FROM t;
+------+
| val |
+------+
| 3 |
| 100 |
| NULL |
| NULL |
| 9 |
+------+
Normally, sorting groups the NULL values at the beginning for an ascending sort:
mysql> SELECT val FROM t ORDER BY val;
+------+
| val |
+------+
| NULL |
| NULL |
| 3 |
| 9 |
| 100 |
+------+
To put them at the end instead, without changing the order of other values, introduce
an extra ORDER BY column that maps NULL values to a higher value than non- NULL values:
mysql> SELECT val FROM t ORDER BY IF(val IS NULL,1,0), val;
+------+
| val |
+------+
| 3 |
| 9 |
| 100 |
| NULL |
| NULL |
+------+
The IF() expression creates a new column for the sort that is used as the primary sort
value.
For descending sorts, NULL values group at the end. To put them at the beginning instead,
use the same technique, but reverse the second and third arguments of the IF() function
to map NULL values to a lower value than non- NULL values:
IF(val IS NULL,0,1)
The same technique is useful for floating values other than NULL to either end of the sort
order. Suppose that you want to sort mail table messages in sender/recipient order, but
you want to put messages for a particular sender first. In the real world, the most