MySQL Cookbook

6.17 Floating Specific Values to the Head or Tail of the Sort Order

6.17.1 Problem

You want a column to sort the way it normally does, except for a few values that you want at a specific spot.

6.17.2 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.

6.17.3 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 | gene    | barb    |    2291 |
| 2001-05-16 09:00:28 | gene    | barb    |     613 |
| 2001-05-15 07:17:48 | gene    | gene    |    3824 |
| 2001-05-15 17:35:31 | gene    | gene    |    3856 |
| 2001-05-19 22:21:51 | gene    | gene    |   23992 |
| 2001-05-15 10:25:52 | gene    | tricia  |  998532 |
| 2001-05-12 12:48:13 | tricia  | gene    |  194925 |
| 2001-05-14 17:03:01 | tricia  | phil    | 2394482 |
+---------------------+---------+---------+---------+

The value of the extra sort column is 0 for rows where the srcuser value is phil, and 1 for all other rows. By making that the most significant sort column, records 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 records 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    |
+---------------------+---------+---------+---------+
| 2001-05-14 14:42:21 | barb    | barb    |   98151 |
| 2001-05-15 07:17:48 | gene    | gene    |    3824 |
| 2001-05-15 17:35:31 | gene    | gene    |    3856 |
| 2001-05-19 22:21:51 | gene    | gene    |   23992 |
| 2001-05-12 15:02:49 | phil    | phil    |    1048 |
| 2001-05-15 08:50:57 | phil    | phil    |     978 |
| 2001-05-11 10:15:08 | barb    | tricia  |   58274 |
| 2001-05-13 13:59:18 | barb    | tricia  |     271 |
| 2001-05-14 09:31:37 | gene    | barb    |    2291 |
| 2001-05-16 09:00:28 | gene    | barb    |     613 |
| 2001-05-15 10:25:52 | gene    | tricia  |  998532 |
| 2001-05-16 23:04:19 | phil    | barb    |   10294 |
| 2001-05-14 11:52:17 | phil    | tricia  |    5781 |
| 2001-05-17 12:49:23 | phil    | tricia  |     873 |
| 2001-05-12 12:48:13 | tricia  | gene    |  194925 |
| 2001-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 (assuming 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    |
+---------------------+---------+---------+---------+
| 2001-05-14 14:42:21 | barb    | barb    |   98151 |
| 2001-05-15 07:17:48 | gene    | gene    |    3824 |
| 2001-05-15 17:35:31 | gene    | gene    |    3856 |
| 2001-05-19 22:21:51 | gene    | gene    |   23992 |
| 2001-05-12 15:02:49 | phil    | phil    |    1048 |
| 2001-05-15 08:50:57 | phil    | phil    |     978 |
| 2001-05-11 10:15:08 | barb    | tricia  |   58274 |
| 2001-05-13 13:59:18 | barb    | tricia  |     271 |
| 2001-05-14 09:31:37 | gene    | barb    |    2291 |
| 2001-05-16 09:00:28 | gene    | barb    |     613 |
| 2001-05-15 10:25:52 | gene    | tricia  |  998532 |
| 2001-05-16 23:04:19 | phil    | barb    |   10294 |
| 2001-05-14 11:52:17 | phil    | tricia  |    5781 |
| 2001-05-17 12:49:23 | phil    | tricia  |     873 |
| 2001-05-12 12:48:13 | tricia  | gene    |  194925 |
| 2001-05-14 17:03:01 | tricia  | phil    | 2394482 |
+---------------------+---------+---------+---------+