Database Reference
In-Depth Information
3.3. Sorting Query Results
Problem
Your query results aren't sorted the way you want.
Solution
MySQL can't read your mind. Use an
ORDER
BY
clause to tell it how to sort result rows.
Discussion
When you select rows, the MySQL server is free to return them in any order unless you
instruct it otherwise by saying how to sort the result. There are lots of ways to use sorting
techniques, as
Chapter 7
explores in detail. Briefly, to sort a result set, add an
ORDER
BY
clause that names the column or columns to use for sorting. This statement names
multiple columns in the
ORDER
BY
clause to sort rows by host and by user within each
host:
mysql>
SELECT * FROM mail WHERE dstuser = 'tricia'
->
ORDER BY srchost, srcuser;
+---------------------+---------+---------+---------+---------+--------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+--------+
| 2014-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2014-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2014-05-19 12:49:23 | phil | mars | tricia | saturn | 873 |
| 2014-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2014-05-12 18:59:18 | barb | saturn | tricia | venus | 271 |
+---------------------+---------+---------+---------+---------+--------+
To sort a column in reverse (descending) order, add the keyword
DESC
after its name in
the
ORDER
BY
clause:
mysql>
SELECT * FROM mail WHERE size > 50000 ORDER BY size DESC;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2014-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 |
| 2014-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2014-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2014-05-14 14:42:21 | barb | venus | barb | venus | 98151 |
| 2014-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
+---------------------+---------+---------+---------+---------+---------+
