MySQL Cookbook

6.4 Sorting Expression Results

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

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

6.4.3 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 refer to the alias:^[1]

^[1] 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 Recipe 7.13.

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

These techniques work for MySQL 3.23.2 and up, too, but you also have the additional option of putting 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) |
+---------------------+---------+-------------------------+
| 2001-05-11 10:15:08 | barb    |                      57 |
| 2001-05-14 14:42:21 | barb    |                      96 |
| 2001-05-12 12:48:13 | tricia  |                     191 |
| 2001-05-15 10:25:52 | gene    |                     976 |
| 2001-05-14 17:03:01 | tricia  |                    2339 |
+---------------------+---------+-------------------------+

However, even if you can put the expression in the ORDER BY clause, there are at least two reasons you might still want to use an alias:

• It's easier to write the ORDER BY clause using the alias than by repeating the (rather cumbersome) expression.

• The alias may be useful for display purposes, to provide a more meaningful column label.

The same restriction on expressions in ORDER BY clauses applies to GROUP BY (which we'll get to in Chapter 7), and the same workarounds apply as well. If your version of MySQL is older than 3.23.2, be sure to remember these workarounds. Many of the queries in the rest of this book use expressions in ORDER BY or GROUP BY clauses; to use them with an older MySQL server, you'll need to rewrite them using the techniques just described.