String Functions - Learning MySQL and MariaDB - page 230

+------------+-----------------+----------------------------+
| 11 | Arctic Loon | artic_loon_male.jpg |
| 12 | Pacific Loon | pacific_loon_male.jpg |
| 111 | Wilson's Plover | wilson_plover_male.jpg |
| 112 | Wilson's Plover | wilson_plover_female.jpg |
| 113 | Wilson's Plover | wilson_plover_juvenile.jpg |
+------------+-----------------+----------------------------+
That worked correctly. Let's suppose now that we don't want to use sorting_id , but
instead the gender_age column. This is an ENUM columnspecifying that the image file
is for a male , female , or a juvenile . The color patterns of most birds deviate based
on these factors. Let's see how the results will look if we sort based on this column:
SELECT bird_name, gender_age, bird_image
FROM bird_images
WHERE bird_name LIKE '%Plover%'
ORDER BY gender_age
LIMIT 5;
+-----------------+------------+----------------------------+
| bird_name | gender_age | bird_image |
+-----------------+------------+----------------------------+
| Wilson's Plover | male | wilson_plover_male.jpg |
| Snowy Plover | male | snowy_plover_male.jpg |
| Wilson's Plover | female | wilson_plover_female.jpg |
| Snowy Plover | female | snowy_plover_female.jpg |
| Wilson's Plover | juvenile | wilson_plover_juvenile.jpg |
+-----------------+------------+----------------------------+
Notice that the rows are grouped together based on the gender_age column, but those
values are not in alphabetical order (i.e., female rows should be before male rows). This is
because of how the enumerated values are listed in the gender_age column:
SHOW COLUMNS FROM bird_images LIKE 'gender_age' \G
*************************** 1. row ***************************
Field: gender_age
Type: enum('male','female','juvenile')
Null: YES
Key:
Default: NULL
Extra:
To MySQL, the value of male for the gender_age column is stored as 1, and female
as 2. This controls the order of the display, even though the values are rendered as text. If
we use though the CAST() or the CONVERT() function inthe ORDER BY clause,