Sorting Query Results - MySQL - page 266

| 2014-05-15 10:25:52 | gene | tricia | 998532 |
| 2014-05-16 23:04:19 | phil | barb | 10294 |
| 2014-05-14 11:52:17 | phil | tricia | 5781 |
| 2014-05-19 12:49:23 | phil | tricia | 873 |
| 2014-05-12 12:48:13 | tricia | gene | 194925 |
| 2014-05-14 17:03:01 | tricia | phil | 2394482 |
+---------------------+---------+---------+---------+
If you have a pretty good idea about the contents of your table, it's sometimes possible
to 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):
SELECT t , srcuser , dstuser , size
FROM mail
ORDER BY IF ( srcuser = dstuser , NULL , srcuser ), dstuser ;
7.12. Defining a Custom Sort Order
Problem
You want to sort values in a nonstandard order.
Solution
Use FIELD() to map column values to a sequence that places the values in the desired
order.
Discussion
Recipe 7.11 shows how to make a specific group of rows float to the head of the sort
order. To impose a specific order on all values in a column, use the FIELD() function
to map them to a list of numeric values and use the numbers for sorting. FIELD()
compares its first argument to the following arguments and returns an integer indicating
which one it matches. (This works best when the column contains a small number of
distinct values.)
The following FIELD() call compares value to str1 , str2 , str3 , and str4 , and returns
1, 2, 3, or 4, depending on which of them value is equal to:
FIELD( value , str1 , str2 , str3 , str4 )
If value is NULL or none of the values match, FIELD() returns 0.
You can use FIELD() to sort an arbitrary set of values into any order you please. For
example, to display driver_log rows for Henry, Suzi, and Ben, in that order, do this:
mysql> SELECT * FROM driver_log
-> ORDER BY FIELD(name,'Henry','Suzi','Ben');