Database Reference
In-Depth Information
Probably everyone tries something like that sooner or later, but it doesn't work. Aggre‐
gate functions such as
MIN()
and
MAX()
cannot be used in
WHERE
clauses, which require
expressions that apply to individual rows. The intent of the statement is to determine
which row has the maximum population value and display the associated state name.
The problem is that although you and I know perfectly well what we mean by writing
such a thing, it makes no sense at all in SQL. The statement fails because SQL uses the
WHERE
clause to determine which rows to select, but the value of an aggregate function
is known only
after
selecting the rows from which the function's value is determined!
So, in a sense, the statement is self-contradictory. To solve this problem, save the max‐
imum population value in a user-defined variable, then compare rows to the variable
value:
mysql>
SET @max = (SELECT MAX(pop) FROM states);
mysql>
SELECT pop AS 'highest population', name FROM states WHERE pop = @max;
+--------------------+------------+
| highest population | name |
+--------------------+------------+
| 37253956 | California |
+--------------------+------------+
Alternatively, for a single-statement solution, use a subquery in the
WHERE
clause that
returns the maximum population value:
SELECT
pop
AS
'highest population'
,
name
FROM
states
WHERE
pop
=
(
SELECT
MAX
(
pop
)
FROM
states
);
This technique also works even if the minimum or maximum value itself isn't actually
contained in the row, but is only derived from it. To determine the length of the shortest
verse in the King James Version, do this:
mysql>
SELECT MIN(CHAR_LENGTH(vtext)) FROM kjv;
+-------------------------+
| MIN(CHAR_LENGTH(vtext)) |
+-------------------------+
| 11 |
+-------------------------+
If you want to know “Which verse is that?” do this instead:
mysql>
SELECT bname, cnum, vnum, vtext FROM kjv
->
WHERE CHAR_LENGTH(vtext) = (SELECT MIN(CHAR_LENGTH(vtext)) FROM kjv);
+-------+------+------+-------------+
| bname | cnum | vnum | vtext |
+-------+------+------+-------------+
| John | 11 | 35 | Jesus wept. |
+-------+------+------+-------------+
Yet another way to select other columns from rows containing a minimum or maximum
value is to use a join. Select the value into another table, then join it to the original table
to select the row that matches the value. To find the row for the state with the highest
population, use a join like this:
