MySQL Cookbook, 2nd Edition

Using Expressions for Sorting

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.

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    |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2006-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2006-05-12 15:02:49 | phil    | mars    | phil    | saturn  |    1048 |
| 2006-05-13 13: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)

Wondering about the +1023 in the FLOOR() expression? That’s there so that size values group to the nearest upper boundary of the 1024-byte categories. Without it, the values group by lower boundaries (for example, a 2047-byte message would be reported as having a size of 1 kilobyte rather than 2). This technique is discussed in more detail in Categorizing Noncategorical Data.

There are two ways ...