Sorting Expression Results
Problem
You want to sort a query result based on values calculated from a column, rather than using the values actually stored in the column.
Solution
Put the expression that calculates the values in the
ORDER
BY
clause. For older
versions of MySQL that don’t support
ORDER
BY
expressions, use a
workaround.
Discussion
One of the columns in the mail
table shows how
large each mail message is, in bytes:
mysql> SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
| 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 |
...
Suppose you want to retrieve records 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. You can use
ORDER
BY
to sort expression
results, although the way you write the query may depend on your
version of MySQL.
Prior to MySQL 3.23.2, expressions in
ORDER
BY
clauses are not allowed. To work around this problem, specify the expression in the output column list and either refer to it by position or give it an alias and ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.