Controlling String Case Sensitivity for MIN( ) and MAX( )
Problem
MIN( ) and MAX( ) select
strings in case sensitive fashion when you don’t
want them to, or vice versa.
Solution
Alter the case sensitivity of the strings.
Discussion
When applied
to string values, MIN( ) and MAX( ) produce results determined according to lexical sorting
rules. One factor in string sorting is case sensitivity, so
MIN( ) and MAX( ) are affected
by that as well. In Chapter 6, we used a
textblob_val table containing two columns of
apparently identical values:
mysql> SELECT tstr, bstr FROM textblob_val;
+------+------+
| tstr | bstr |
+------+------+
| aaa | aaa |
| AAA | AAA |
| bbb | bbb |
| BBB | BBB |
+------+------+However, although the values look the same, they
don’t behave the same. bstr is a
BLOB column and is case sensitive.
tstr, a TEXT column, is not. As
a result, MIN( ) and MAX( )
will not necessarily produce the same results for the two columns:
mysql> SELECT MIN(tstr), MIN(bstr) FROM textblob_val;
+-----------+-----------+
| MIN(tstr) | MIN(bstr) |
+-----------+-----------+
| aaa | AAA |
+-----------+-----------+To make tstr case sensitive, use
BINARY:
mysql> SELECT MIN(BINARY tstr) FROM textblob_val;
+------------------+
| MIN(BINARY tstr) |
+------------------+
| AAA |
+------------------+To make bstr not case sensitive, you can convert
the values to a given lettercase:
mysql> SELECT MIN(LOWER(bstr)) FROM textblob_val; +------------------+ | MIN(LOWER(bstr)) | +------------------+ | aaa | ...