MySQL Cookbook, 2nd Edition

Sorting in User-Defined Orders

Problem

You want to define a nonstandard sort order for the values in a column.

Solution

Use FIELD() to map column values to a sequence that places the values in the desired order.

Discussion

Recipe 7.14 showed how to make a specific group of rows go to the head of the sort order. If you want 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 a number indicating which one of them it matches. The following FIELD() call compares value to str1, str2, str3, and str4, and returns 1, 2, 3, or 4, depending on which one of them value is equal to:

FIELD(value,str1,str2,str3,str4)

The number of comparison values need not be four; FIELD() takes a variable-length argument list. If value is NULL or none of the values match, FIELD() returns 0.

FIELD() can be used 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'); +--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 10 | Henry | 2006-08-30 | 203 | | 8 | Henry | 2006-09-01 | 197 | | 6 | Henry | 2006-08-26 | 115 | | 4 | Henry | 2006-08-27 | 96 | | 3 | Henry | 2006-08-29 | 300 | | 7 | Suzi | 2006-09-02 | 502 | ...