Sorting Query Results - MySQL - page 245

| bbb |
+---------+
You get the same result for a nonbinary string column that has a binary collation,
as long as the column contains single-byte characters (for example, CHAR(3) CHAR
ACTER SET latin1 COLLATE latin1_bin ). For multibyte characters, a binary colla‐
tion still produces a numeric sort, but the character values use multibyte numbers.
To alter the sorting properties of each column, use the techniques described in Recipe 5.7
for controlling string comparisons:
• To sort case-insensitive strings in case-sensitive fashion, order the sorted values
using a case-sensitive collation:
mysql> SELECT ci_str FROM str_val
-> ORDER BY ci_str COLLATE latin1_general_cs;
+--------+
| ci_str |
+--------+
| AAA |
| aaa |
| BBB |
| bbb |
+--------+
• To sort case-sensitive strings in case-insensitive fashion, order the sorted values
using a case-insensitive collation:
mysql> SELECT cs_str FROM str_val
-> ORDER BY cs_str COLLATE latin1_swedish_ci;
+--------+
| cs_str |
+--------+
| AAA |
| aaa |
| bbb |
| BBB |
+--------+
Alternatively, sort using values that have been converted to the same lettercase,
which makes lettercase irrelevant:
mysql> SELECT cs_str FROM str_val
-> ORDER BY UPPER(cs_str);
+--------+
| cs_str |
+--------+
| AAA |
| aaa |
| bbb |
| BBB |
+--------+