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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.