Sorting Query Results - MySQL - page 252

+------------+----------+--------+---------+
| DIN40672US | DIN | 40672 | US |
| KIT00372UK | KIT | 00372 | UK |
| KIT01729JP | KIT | 01729 | JP |
| BED00038SG | BED | 00038 | SG |
| BTH00485US | BTH | 00485 | US |
| BTH00415JP | BTH | 00415 | JP |
+------------+----------+--------+---------+
Those fixed-length substrings of the id values can be used for sorting, either alone or
in combination. For example, to sort by product category, extract and use the category
in the ORDER BY clause:
mysql> SELECT * FROM housewares ORDER BY LEFT(id,3);
+------------+------------------+
| id | description |
+------------+------------------+
| BED00038SG | bedside lamp |
| BTH00485US | shower stall |
| BTH00415JP | lavatory |
| DIN40672US | dining table |
| KIT00372UK | garbage disposal |
| KIT01729JP | microwave oven |
+------------+------------------+
To sort by product serial number, use MID() to extract the middle five characters from
the id values, beginning with the fourth:
mysql> SELECT * FROM housewares ORDER BY MID(id,4,5);
+------------+------------------+
| id | description |
+------------+------------------+
| BED00038SG | bedside lamp |
| KIT00372UK | garbage disposal |
| BTH00415JP | lavatory |
| BTH00485US | shower stall |
| KIT01729JP | microwave oven |
| DIN40672US | dining table |
+------------+------------------+
This appears to be a numeric sort, but it's actually a string sort because MID() returns
strings. The lexical and numeric sort order are the same in this case because the “num‐
bers” have leading zeros to make them all the same length.
To sort by country code, use the rightmost two characters of the id values ( ORDER BY
RIGHT(id,2) ).
You can also sort using combinations of substrings; for example, by country code and
serial number within country:
mysql> SELECT * FROM housewares ORDER BY RIGHT(id,2), MID(id,4,5);
+------------+------------------+
| id | description |