Database Reference
In-Depth Information
• Binary strings sort using numeric byte values, so there is no concept of lettercase
involved. However, because letters in different cases have different byte values,
comparisons of binary strings effectively are case sensitive. (That is, a and A are
unequal.) To sort binary strings using a case-insensitive ordering, convert them to
nonbinary strings and apply an appropriate collation. For example, to perform a
case-insensitive sort, use a statement like this:
mysql> SELECT bin_str FROM str_val
-> ORDER BY CONVERT(bin_str USING latin1) COLLATE latin1_swedish_ci;
+---------+
| bin_str |
+---------+
| AAA |
| aaa |
| bbb |
| BBB |
+---------+
If the character-set default collation is case insensitive (as is true for latin1 ), you
can omit the COLLATE clause.
7.5. Date-Based Sorting
Problem
You want to sort rows in temporal order.
Solution
Sort using a date or time column. If some parts of the values are irrelevant for the sort
that you want to accomplish, ignore them.
Discussion
Many database tables include date or time information and it's very often necessary to
sort results in temporal order. MySQL knows how to sort temporal data types, so there's
no special trick to ordering them. The next few examples use the mail table, which
contains a DATETIME column, but the same sorting principles apply to DATE , TIME , and
TIMESTAMP columns.
Here are the messages sent by phil :
mysql> SELECT * FROM mail WHERE srcuser = 'phil';
+---------------------+---------+---------+---------+---------+-------+
| t | srcuser | srchost | dstuser | dsthost | size |
+---------------------+---------+---------+---------+---------+-------+
| 2014-05-12 15:02:49 | phil | mars | phil | saturn | 1048 |
Search WWH ::




Custom Search