6.18 Sorting in User-Defined Orders
6.18.1 Problem
You want to define the sort order for all values in a column.
6.18.2 Solution
Use FIELD( ) to map column values onto a sequence
that places the values in the desired order.
6.18.3 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 | 391 |
| 7 | Suzi | 2001-12-02 | 502 |
| 1 | Ben | 2001-11-30 | 152 |
| 5 | Ben | 2001-11-29 | 131 |
| 9 | Ben | 2001-12-02 | 79 |
+--------+-------+------------+-------+
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 specific orders when the
categories don't sort naturally into any useful
sequence.
|
