Sorting Query Results - MySQL - page 267

+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 10 | Henry | 2014-07-30 | 203 |
| 8 | Henry | 2014-08-01 | 197 |
| 6 | Henry | 2014-07-26 | 115 |
| 4 | Henry | 2014-07-27 | 96 |
| 3 | Henry | 2014-07-29 | 300 |
| 7 | Suzi | 2014-08-02 | 502 |
| 2 | Suzi | 2014-07-29 | 391 |
| 5 | Ben | 2014-07-29 | 131 |
| 9 | Ben | 2014-08-02 | 79 |
| 1 | Ben | 2014-07-30 | 152 |
+--------+-------+------------+-------+
7.13. Sorting ENUM Values
Problem
ENUM values don't sort like other string columns.
Solution
Learn how they work, and exploit those properties to your advantage.
Discussion
ENUM is a string data type, but ENUM values actually are stored numerically with values
ordered the same way they are listed in the table definition. These numeric values affect
how enumerations are sorted, which can be very useful. Suppose that a table named
weekday contains an enumeration column named day that has weekday names as its
members:
CREATE TABLE weekday
(
day ENUM ( 'Sunday' , 'Monday' , 'Tuesday' , 'Wednesday' ,
'Thursday' , 'Friday' , 'Saturday' )
);
Internally, MySQL defines the enumeration values Sunday through Saturday in that
definition to have numeric values from 1 to 7. To see this for yourself, create the table
using the definition just shown, and then insert into it a row for each day of the week.
To make the insertion order differ from sorted order (so that you can see the effect of
sorting), add the days in random order:
mysql> INSERT INTO weekday (day) VALUES('Monday'),('Friday'),
-> ('Tuesday'), ('Sunday'), ('Thursday'), ('Saturday'), ('Wednesday');