MySQL Cookbook, 2nd Edition

Displaying One Set of Values While Sorting by Another

Problem

You want to sort a result set using values that you’re not selecting.

Solution

That’s not a problem. You can use columns in the ORDER BY clause that don’t appear in the output column list.

Discussion

ORDERBY is not limited to sorting only those columns named in the output column list. It can sort using values that are“hidden” (that is, not displayed in the query output). This technique is commonly used when you have values that can be represented different ways and you want to display one type of value but sort by another. For example, you may want to display mail message sizes not in terms of bytes, but as strings such as 103K for 103 kilobytes. You can convert a byte count to that kind of value using this expression:

CONCAT(FLOOR((size+1023)/1024),'K')

However, such values are strings, so they sort lexically, not numerically. If you use them for sorting, a value such as 96K sorts after 2339K, even though it represents a smaller number:

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

To achieve ...