MySQL Cookbook

Sorting in User-Defined Orders

Problem

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

Solution

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

Discussion

The previous section 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 records 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 | +--------+-------+------------+-------+ | 3 | Henry | 2001-11-29 | 300 | | 4 | Henry | 2001-11-27 | 96 | | 6 | Henry | 2001-11-26 | 115 | | 8 | Henry | 2001-12-01 | 197 | | 10 | Henry | 2001-11-30 | 203 | | 2 | Suzi | 2001-11-29 ...