Sorting Query Results - MySQL - page 241

jersey_num CHAR ( 3 ) # jersey number
);
Then the jersey numbers will display the same way you enter them, and 0 and 00 will
be treated as distinct values. Unfortunately, although representing numbers as strings
solves the problem of distinguishing 0 and 00 , it introduces a different problem. Suppose
that a team has the following players:
mysql> SELECT name, jersey_num FROM roster;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Lynne | 29 |
| Ella | 0 |
| Elizabeth | 100 |
| Nancy | 00 |
| Jean | 8 |
| Sherry | 47 |
+-----------+------------+
Now try to sort the team members by jersey number. If those numbers are stored as
strings, they sort lexically, and lexical order often differs from numeric order. That's
certainly true for the team in question:
mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Ella | 0 |
| Nancy | 00 |
| Elizabeth | 100 |
| Lynne | 29 |
| Sherry | 47 |
| Jean | 8 |
+-----------+------------+
The values 100 and 8 are out of place, but that's easily solved: display the string values
and use the numeric values for sorting. To accomplish this, add zero to the jer
sey_num values to force a string-to-number conversion:
mysql> SELECT name, jersey_num FROM roster ORDER BY jersey_num+0;
+-----------+------------+
| name | jersey_num |
+-----------+------------+
| Ella | 0 |
| Nancy | 00 |
| Jean | 8 |
| Lynne | 29 |
| Sherry | 47 |
| Elizabeth | 100 |
+-----------+------------+