Database Reference
In-Depth Information
mysql>
CREATE TEMPORARY TABLE tmp SELECT MAX(pop) as maxpop FROM states;
mysql>
SELECT states.* FROM states INNER JOIN tmp
->
ON states.pop = tmp.maxpop;
+------------+--------+------------+----------+
| name | abbrev | statehood | pop |
+------------+--------+------------+----------+
| California | CA | 1850-09-09 | 37253956 |
+------------+--------+------------+----------+
See Also
Recipe 14.7
extends the discussion here to the problem of finding rows that contain
minimum or maximum values for multiple groups in a dataset.
8.4. 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 comparison characteristics of the strings.
Discussion
Recipe 5.1
discusses how string-comparison properties depend on whether the strings
are binary or nonbinary:
• Binary strings are sequences of bytes. They are compared byte by byte using nu‐
meric byte values. Character set and lettercase have no meaning for comparisons.
• Nonbinary strings are sequences of characters. They have a character set and col‐
lation and are compared character by character using the order defined by the
collation.
These properties also apply to string columns used as the argument to the
MIN()
or
MAX()
function because they are based on comparison. To alter how these functions
work with a string column, alter the column's comparison properties.
Recipe 5.7
dis‐
cusses how to control these properties, and
Recipe 7.4
shows how they apply to string
sorts. The same principles apply to finding minimum and maximum string values, so
I'll just summarize here; read
Recipe 7.4
for additional details.
