Database Reference
In-Depth Information
103K for 103 kilobytes. You can convert a byte count to that kind of value using this
expression:
CONCAT(FLOOR((size+1023)/1024),'K')
However, such values are strings, so they sort lexically, not numerically. If you use them
for sorting, a value such as 96K sorts after 2339K , even though it represents a smaller
number:
mysql> SELECT t, srcuser,
-> CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
-> FROM mail WHERE size > 50000
-> ORDER BY size_in_K;
+---------------------+---------+-----------+
| t | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2014-05-12 12:48:13 | tricia | 191K |
| 2014-05-14 17:03:01 | tricia | 2339K |
| 2014-05-11 10:15:08 | barb | 57K |
| 2014-05-14 14:42:21 | barb | 96K |
| 2014-05-15 10:25:52 | gene | 976K |
+---------------------+---------+-----------+
To achieve the desired output order, display the string, but use actual numeric size for
sorting:
mysql> SELECT t, srcuser,
-> CONCAT(FLOOR((size+1023)/1024),'K') AS size_in_K
-> FROM mail WHERE size > 50000
-> ORDER BY size;
+---------------------+---------+-----------+
| t | srcuser | size_in_K |
+---------------------+---------+-----------+
| 2014-05-11 10:15:08 | barb | 57K |
| 2014-05-14 14:42:21 | barb | 96K |
| 2014-05-12 12:48:13 | tricia | 191K |
| 2014-05-15 10:25:52 | gene | 976K |
| 2014-05-14 17:03:01 | tricia | 2339K |
+---------------------+---------+-----------+
Displaying values as strings but sorting them as numbers helps solve some otherwise
difficult problems. Members of sports teams typically are assigned a jersey number,
which normally you might think should be stored using a numeric column. Not so fast!
Some players like to have a jersey number of zero ( 0 ), and some like double-zero ( 00 ).
If a team happens to have players with both numbers, you cannot represent them using
a numeric column because both values will be treated as the same number. To solve this
problem, store jersey numbers as strings:
CREATE TABLE roster
(
name CHAR ( 30 ), # player name
Search WWH ::




Custom Search