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 ...