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

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

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 |
|      2 | Suzi  | 2006-08-29 |   391 |
|      5 | Ben   | 2006-08-29 |   131 |
|      9 | Ben   | 2006-09-02 |    79 |
|      1 | Ben   | 2006-08-30 |   152 |
+--------+-------+------------+-------+

You can use FIELD() with column substrings, too. To sort items from the housewares table by country of manufacture using the order US, UK, JP, SG, do this:

mysql>SELECT id, description FROM housewares
    -> ORDER BY FIELD(RIGHT(id,2),'US','UK','JP','SG'),
+------------+------------------+
| id         | description      |
+------------+------------------+
| DIN40672US | dining table     |
| BTH00485US | shower stall     |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven   |
| BTH00415JP | lavatory         |
| BED00038SG | bedside lamp     |
+------------+------------------+

More generally, FIELD() can be used to sort any kind of category-based values into a specific order when the categories don’t sort naturally into that order.