Database Reference
In-Depth Information
Alternatively, if the sorting expression appears in the output column list, you can alias
it there 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 |
+---------------------+---------+-----------+
| 2014-05-11 10:15:08 | barb | 57 |
| 2014-05-14 14:42:21 | barb | 96 |
| 2014-05-12 12:48:13 | tricia | 191 |
| 2014-05-15 10:25:52 | gene | 976 |
| 2014-05-14 17:03:01 | tricia | 2339 |
+---------------------+---------+-----------+
You might prefer the alias method for several reasons:
• It's easier to write the alias in the
ORDER
BY
clause than to repeat the (cumbersome)
expression.
• Without the alias, if you change the expression one place, you must change it in the
other.
• The alias may be useful for display purposes, to provide a better column label. Note
how the third column heading is much more meaningful in the second of the two
preceding queries.
7.3. Displaying One Set of Values While Sorting by
Another
Problem
You want to sort a result set using values that don't appear in the output column list.
Solution
That's not a problem. The
ORDER
BY
clause can refer to columns you don't display.
Discussion
ORDER
BY
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 dif‐
ferent 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
