Archive for March, 2013

MySQL: sort by case sensitive varchar

Figuring out how to apply unique index on a case sensitive varchar column turned out to be only a part of the solution.

Another thing to take into account is sorting. Once a column is case sensitive, ORDER BY starts working in a bit different way:

Abc.txt
Bcd.txt
Cde.txt
abc.txt
bcd.txt
cde.txt

To address this ORDER BY should look as follows:

SELECT name FROM file
ORDER BY CAST(name AS CHAR) ASC, BINARY name DESC;

In this case the result set is sorted “as usual”:

Abc.txt
abc.txt
Bcd.txt
bcd.txt
Cde.txt
cde.txt