MySQL Cookbook

Floating Specific 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 a specific spot.

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. We used this technique earlier to float NULL values to the high end of the sort order (see Recipe 6.6), but it works for other types of information as well. Suppose you want to sort mail table messages in sender/recipient order, with the exception that you want to put messages for phil first. You can do that like this:

mysql> SELECT t, srcuser, dstuser, size
    -> FROM mail
    -> ORDER BY IF(srcuser='phil',0,1), srcuser, dstuser; +---------------------+---------+---------+---------+ | t | srcuser | dstuser | size | +---------------------+---------+---------+---------+ | 2001-05-16 23:04:19 | phil | barb | 10294 | | 2001-05-12 15:02:49 | phil | phil | 1048 | | 2001-05-15 08:50:57 | phil | phil | 978 | | 2001-05-14 11:52:17 | phil | tricia | 5781 | | 2001-05-17 12:49:23 | phil | tricia | 873 | | 2001-05-14 14:42:21 | barb | barb | 98151 | | 2001-05-11 10:15:08 | barb | tricia | 58274 | | 2001-05-13 13:59:18 | barb | tricia | 271 | | 2001-05-14 09:31:37 ...