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.
3.144.37.38