MySQL can’t read your mind. Add an
ORDER
BY
clause to tell it exactly how you want
result rows sorted.
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. Chapter 7 explores this topic in detail. Briefly, you sort
a result set by adding an ORDER
BY
clause that names the column or
columns that you want to use for sorting. The following statement
sorts rows by size:
mysql>SELECT * FROM mail WHERE size > 100000 ORDER BY size;
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2006-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 |
+---------------------+---------+---------+---------+---------+---------+
This statement names multiple columns in the ORDER
BY
clause to sort rows by host, and then by user within each host:
mysql>SELECT * FROM mail WHERE dstuser = 'tricia'
->ORDER BY srchost, srcuser;
+---------------------+---------+---------+---------+---------+--------+ | t | srcuser | srchost | dstuser | dsthost | size | +---------------------+---------+---------+---------+---------+--------+ | 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 | | 2006-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 | | 2006-05-17 12:49:23 | phil | mars | tricia | saturn | 873 | | 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 | | 2006-05-13 13: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 |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-14 17:03:01 | tricia | saturn | phil | venus | 2394482 |
| 2006-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2006-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
| 2006-05-14 14:42:21 | barb | venus | barb | venus | 98151 |
| 2006-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
+---------------------+---------+---------+---------+---------+---------+
18.222.93.132