For example, I'd like to order the "weight" column ascend, but put the nulls last.
mysql> select product_name, weight from products order by 2;
+--------------+--------+
| product_name | weight |
+--------------+--------+
| TV | NULL |
| NB | 1 |
| PC | 2 |
| PR | 3 |
+--------------+--------+
4 rows in set (0.01 sec)Most of rows are ordered as I wish, except the first row with nulled value.
Let try to sort them descend.
mysql> select product_name, weight from products order by 2 desc;
+--------------+--------+
| product_name | weight |
+--------------+--------+
| PR | 3 |
| PC | 2 |
| NB | 1 |
| TV | NULL |
+--------------+--------+
4 rows in set (0.00 sec)Nope, this is not what we want, neither.
Of course, we can change the NULL into a solid value by coalescing.
mysql> select product_name, coalesce(display_order, 999999) weight from products order by 2;
+--------------+--------+
| product_name | weight |
+--------------+--------+
| NB | 1 |
| PC | 2 |
| PR | 3 |
| TV | 999999 |
+--------------+--------+
4 rows in set (0.00 sec)But the drawback is that we changed the output values.
Alternatively, you can put coalesce in the order clause.
mysql> select product_name, weight from products order by coalesce(weight, 999999);
+--------------+--------+
| product_name | weight |
+--------------+--------+
| NB | 1 |
| PC | 2 |
| PR | 3 |
| TV | NULL |
+--------------+--------+
4 rows in set (0.00 sec)It's lots better, so far, we should satisfy with this, but it can not cover all the situations (e.g. sorting a character-based column), even you use a very large value.
Please use "~" character as the substitute to put the NULLs to the last. It can cover more situations if you are not too picky.
mysql> select product_name, weight from products order by coalesce(weight, '~');
+--------------+--------+
| product_name | weight |
+--------------+--------+
| NB | 1 |
| PC | 2 |
| PR | 3 |
| TV | NULL |
+--------------+--------+
4 rows in set (0.00 sec)