Database Reference
In-Depth Information
| 2014-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2014-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2014-05-16 23:04:19 | phil | venus | barb | venus | 10294 |
| 2014-05-19 12:49:23 | phil | mars | tricia | saturn | 873 |
+---------------------+---------+---------+---------+---------+-------+
To display the messages, most recently sent ones first, use
ORDER
BY
with
DESC
:
mysql>
SELECT * FROM mail WHERE srcuser = 'phil' ORDER BY t DESC;
+---------------------+---------+---------+---------+---------+-------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+-------+
| 2014-05-19 12:49:23 | phil | mars | tricia | saturn | 873 |
| 2014-05-16 23:04:19 | phil | venus | barb | venus | 10294 |
| 2014-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2014-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2014-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
+---------------------+---------+---------+---------+---------+-------+
Sometimes a temporal sort uses only part of a date or time column. In that case, use an
expression that extracts the part or parts you need and sort the result using the expres‐
sion. Some examples of this are given in the following discussion.
Sorting by time of day
You can do time-of-day sorting different ways, depending on your column type. If the
values are stored in a
TIME
column named
timecol
, just sort them directly using
OR
DER
BY
timecol
. To put
DATETIME
or
TIMESTAMP
values in time-of-day order, extract the
time parts and sort them. For example, the
mail
table contains
DATETIME
values, which
can be sorted by time of day like this:
mysql>
SELECT * FROM mail ORDER BY TIME(t);
+---------------------+---------+---------+---------+---------+---------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+---------+
| 2014-05-15 07:17:48 | gene | mars | gene | saturn | 3824 |
| 2014-05-15 08:50:57 | phil | venus | phil | venus | 978 |
| 2014-05-16 09:00:28 | gene | venus | barb | mars | 613 |
| 2014-05-14 09:31:37 | gene | venus | barb | mars | 2291 |
| 2014-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 |
| 2014-05-15 10:25:52 | gene | mars | tricia | saturn | 998532 |
| 2014-05-14 11:52:17 | phil | mars | tricia | saturn | 5781 |
| 2014-05-12 12:48:13 | tricia | mars | gene | venus | 194925 |
…
Sorting by calendar day
To sort date values in calendar order, ignore the year part of the dates and use only the
month and day to order values by where they fall during the calendar year. Suppose that
an
occasion
table looks like this when values are ordered by date:
