Summarizing with MIN() and MAX()
Problem
You need to determine the smallest or largest of a set of values.
Solution
Use MIN()
to find the
smallest value, MAX()
to find
the largest.
Discussion
Finding smallest or largest values is somewhat akin to sorting,
except that instead of producing an entire set of sorted values, you
select only a single value at one end or the other of the sorted
range. This kind of operation applies to questions about smallest,
largest, oldest, newest, most expensive, least expensive, and so
forth. One way to find such values is to use the MIN()
and MAX()
functions. (Another way to
address these questions is to use
LIMIT
; see the
discussions in Recipes and .)
Because MIN()
and
MAX()
determine the extreme
values in a set, they’re useful for characterizing ranges:
What date range is represented by the rows in the
mail
table? What are the smallest and largest messages sent?mysql>
SELECT
->MIN(t) AS earliest, MAX(t) AS latest,
->MIN(size) AS smallest, MAX(size) AS largest
->FROM mail;
+---------------------+---------------------+----------+---------+ | earliest | latest | smallest | largest | +---------------------+---------------------+----------+---------+ | 2006-05-11 10:15:08 | 2006-05-19 22:21:51 | 271 | 2394482 | +---------------------+---------------------+----------+---------+What are the shortest and longest trips in the
driver_log
table?
mysql>
SELECT MIN(miles) AS shortest, MAX(miles) AS longest
->FROM driver_log;
+----------+---------+ | shortest | ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.