Recipe 7.14. 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, anyway. Normally, NULL values form a group at the beginning of the sort order (or at the end, if you specify DESC). If you want NULL values at a specific end of the sort order, you can force them to be placed where you want. Suppose that you have a table t with the following contents:

mysql> SELECT val FROM t; +------+ | val  | +------+ |    3 | |  100 | | NULL | | NULL | |    9 | +------+ 

Normally, sorting puts 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, 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 values 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 interesting sender might be postmaster or root. Those names don't appear in the table, so let's use phil as the name of interest instead:

mysql> SELECT t, srcuser, dstuser, size     -> FROM mail     -> ORDER BY IF(srcuser='phil',0,1), srcuser, dstuser; +---------------------+---------+---------+---------+ | t                   | srcuser | dstuser | size    | +---------------------+---------+---------+---------+ | 2006-05-16 23:04:19 | phil    | barb    |   10294 | | 2006-05-12 15:02:49 | phil    | phil    |    1048 | | 2006-05-15 08:50:57 | phil    | phil    |     978 | | 2006-05-14 11:52:17 | phil    | tricia  |    5781 | | 2006-05-17 12:49:23 | phil    | tricia  |     873 | | 2006-05-14 14:42:21 | barb    | barb    |   98151 | | 2006-05-11 10:15:08 | barb    | tricia  |   58274 | | 2006-05-13 13:59:18 | barb    | tricia  |     271 | | 2006-05-14 09:31:37 | gene    | barb    |    2291 | | 2006-05-16 09:00:28 | gene    | barb    |     613 | | 2006-05-15 17:35:31 | gene    | gene    |    3856 | | 2006-05-15 07:17:48 | gene    | gene    |    3824 | | 2006-05-19 22:21:51 | gene    | gene    |   23992 | | 2006-05-15 10:25:52 | gene    | tricia  |  998532 | | 2006-05-12 12:48:13 | tricia  | gene    |  194925 | | 2006-05-14 17:03:01 | tricia  | phil    | 2394482 | +---------------------+---------+---------+---------+ 

The value of the extra sort column is 0 for rows in which the srcuser value is phil, and 1 for all other rows. By making that the most significant sort column, rows for messages sent by phil float to the top of the output. (To sink them to the bottom instead, either sort the column in reverse order using DESC, or reverse the order of the second and third arguments of the IF⁠(⁠ ⁠ ⁠) function.)

You can also use this technique for particular conditions, not just specific values. To put first those rows where people sent messages to themselves, do this:

mysql> SELECT t, srcuser, dstuser, size     -> FROM mail     -> ORDER BY IF(srcuser=dstuser,0,1), srcuser, dstuser; +---------------------+---------+---------+---------+ | t                   | srcuser | dstuser | size    | +---------------------+---------+---------+---------+ | 2006-05-14 14:42:21 | barb    | barb    |   98151 | | 2006-05-19 22:21:51 | gene    | gene    |   23992 | | 2006-05-15 17:35:31 | gene    | gene    |    3856 | | 2006-05-15 07:17:48 | gene    | gene    |    3824 | | 2006-05-12 15:02:49 | phil    | phil    |    1048 | | 2006-05-15 08:50:57 | phil    | phil    |     978 | | 2006-05-11 10:15:08 | barb    | tricia  |   58274 | | 2006-05-13 13:59:18 | barb    | tricia  |     271 | | 2006-05-16 09:00:28 | gene    | barb    |     613 | | 2006-05-14 09:31:37 | gene    | barb    |    2291 | | 2006-05-15 10:25:52 | gene    | tricia  |  998532 | | 2006-05-16 23:04:19 | phil    | barb    |   10294 | | 2006-05-14 11:52:17 | phil    | tricia  |    5781 | | 2006-05-17 12:49:23 | phil    | tricia  |     873 | | 2006-05-12 12:48:13 | tricia  | gene    |  194925 | | 2006-05-14 17:03:01 | tricia  | phil    | 2394482 | +---------------------+---------+---------+---------+ 

If you have a pretty good idea about the contents of your table, you can sometimes eliminate the extra sort column. For example, srcuser is never NULL in the mail table, so the previous query can be rewritten as follows to use one less column in the ORDER BY clause (this relies on the property that NULL values sort ahead of all non-NULL values):

mysql> SELECT t, srcuser, dstuser, size     -> FROM mail     -> ORDER BY IF(srcuser=dstuser,NULL,srcuser), dstuser; +---------------------+---------+---------+---------+ | t                   | srcuser | dstuser | size    | +---------------------+---------+---------+---------+ | 2006-05-14 14:42:21 | barb    | barb    |   98151 | | 2006-05-19 22:21:51 | gene    | gene    |   23992 | | 2006-05-15 17:35:31 | gene    | gene    |    3856 | | 2006-05-15 07:17:48 | gene    | gene    |    3824 | | 2006-05-12 15:02:49 | phil    | phil    |    1048 | | 2006-05-15 08:50:57 | phil    | phil    |     978 | | 2006-05-11 10:15:08 | barb    | tricia  |   58274 | | 2006-05-13 13:59:18 | barb    | tricia  |     271 | | 2006-05-16 09:00:28 | gene    | barb    |     613 | | 2006-05-14 09:31:37 | gene    | barb    |    2291 | | 2006-05-15 10:25:52 | gene    | tricia  |  998532 | | 2006-05-16 23:04:19 | phil    | barb    |   10294 | | 2006-05-14 11:52:17 | phil    | tricia  |    5781 | | 2006-05-17 12:49:23 | phil    | tricia  |     873 | | 2006-05-12 12:48:13 | tricia  | gene    |  194925 | | 2006-05-14 17:03:01 | tricia  | phil    | 2394482 | +---------------------+---------+---------+---------+ 

See Also

The technique of introducing additional sort columns is useful with UNION queries that produce the union of multiple SELECT statements. You can cause the SELECT results to appear one after the other and sort the rows within each individual SELECT. See Section 12.12 for details.