Recipe 7.15. 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 | |      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.