MySQL Cookbook, 2nd Edition

Floating Values to the Head or Tail of the Sort Order

Problem

You want a column to sort the way it normally does, except for a few values that you want at the beginning or end of the sort order. For example, suppose that you want to sort a list in lexical order except for certain high-priority values that should appear first no matter where they fall in the normal sort order.

Solution

Add another sort column to the ORDER BY clause that places those few values where you want them. The remaining sort columns will have their usual effect for the other values.

Discussion

If you want to sort a result set normally except that you want particular values first, create an additional sort column that is 0 for those values and 1 for everything else. This allows you to float the values to the head of the sort order. To put the values at the tail instead, use the additional column to map the values to 1 and all other values to 0.

For example, when a sorted column contains NULL values, MySQL puts them all together in the sort order (at the beginning for an ascending sort, at the end for a descending sort). It may seem a bit odd that NULL values are grouped, given that (as the following query shows) they are not considered equal in comparisons:

mysql>SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

On the other hand, NULL values conceptually do seem more similar to each other than to non-NULL values, and there’s no good way to distinguish one NULL from another, ...