Recipe 7.2. 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 Section 8.12.

There are two ways to use an expression for sorting query results. First, you can put the expression 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) | +---------------------+---------+-------------------------+ | 2006-05-11 10:15:08 | barb    |                      57 | | 2006-05-14 14:42:21 | barb    |                      96 | | 2006-05-12 12:48:13 | tricia  |                     191 | | 2006-05-15 10:25:52 | gene    |                     976 | | 2006-05-14 17:03:01 | tricia  |                    2339 | +---------------------+---------+-------------------------+ 

Second, if you are sorting by an expression named in the output column list, you can give it an alias and refer to the alias in the ORDER BY clause:

mysql> SELECT t, srcuser, FLOOR((size+1023)/1024) AS kilobytes     -> FROM mail WHERE size > 50000     -> ORDER BY kilobytes; +---------------------+---------+-----------+ | t                   | srcuser | kilobytes | +---------------------+---------+-----------+ | 2006-05-11 10:15:08 | barb    |        57 | | 2006-05-14 14:42:21 | barb    |        96 | | 2006-05-12 12:48:13 | tricia  |       191 | | 2006-05-15 10:25:52 | gene    |       976 | | 2006-05-14 17:03:01 | tricia  |      2339 | +---------------------+---------+-----------+ 

Although you can write the ORDER BY clause either way, there are at least two reasons you might prefer to use the alias method:

• It's easier to write the alias in the ORDER BY clause than to repeat the (rather cumbersome) expressionand if you change one, you'll need to change the other.

• The alias may be useful for display purposes, to provide a more meaningful column label. Note how the third column heading for the second of the two preceding queries is more meaningful.