Database Reference
In-Depth Information
7.2. Using Expressions for Sorting
Problem
You want to sort a query result based on values calculated from a column rather than
the values actually stored in the column.
Solution
Put the expression that calculates the values in the
ORDER
BY
clause.
Discussion
One of the
mail
table columns shows how large each mail message is, in bytes:
mysql>
SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2014-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2014-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2014-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2014-05-12 18:59:18 | barb | saturn | tricia | venus | 271 |
…
Suppose that you want to retrieve rows for “big” mail messages (defined as those larger
than 50,000 bytes), but you want them to be displayed and sorted by sizes in terms of
kilobytes, not bytes. In this case, the values to sort are calculated by an expression:
FLOOR((size+1023)/1024)
The
+1023
in the
FLOOR()
expression groups
size
values to the nearest upper boundary
of the 1,024-byte categories. Without it, the values group by lower boundaries (for ex‐
ample, a 2,047-byte message is reported as having a size of 1 kilobyte rather than 2).
Recipe 8.10
disscusses this technique in more detail.
To sort by that expression, put it directly in the
ORDER
BY
clause:
mysql>
SELECT t, srcuser, FLOOR((size+1023)/1024)
->
FROM mail WHERE size > 50000
->
ORDER BY FLOOR((size+1023)/1024);
+---------------------+---------+-------------------------+
| t | srcuser | FLOOR((size+1023)/1024) |
+---------------------+---------+-------------------------+
| 2014-05-11 10:15:08 | barb | 57 |
| 2014-05-14 14:42:21 | barb | 96 |
| 2014-05-12 12:48:13 | tricia | 191 |
| 2014-05-15 10:25:52 | gene | 976 |
| 2014-05-14 17:03:01 | tricia | 2339 |
+---------------------+---------+-------------------------+
