Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.9Mb
PDF (A4) - 34.0Mb
PDF (RPM) - 33.2Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.1Mb
Eclipse Doc Plugin (TGZ) - 9.0Mb
Eclipse Doc Plugin (Zip) - 11.1Mb
Man Pages (TGZ) - 219.4Kb
Man Pages (Zip) - 322.3Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Maximum of Column per Group

3.6.3 Maximum of Column per Group

Task: Find the highest price per article.

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article;

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

User Comments
  Posted by Marco Gergele on September 30, 2004
There seems to be no NULL=Infinity maximum version. In a column is stored end (type date) with NULL = no end. I need the maximum of that column, which is NULL if a NULL-value exists. That should be easy to implement as a function, but not so easy with the existing function.

The same for sum, avg and so on. The versions with NULL=ignore are usefull most of the time, but sum() of NULL and 5 can sometimes be NULL instead of 5. NULL+5 is NULL.
  Posted by Seth Riedel on July 14, 2010
@Marco:

If you wanted to treat NULL = infinity, you could try something like this:

SELECT `id`, MAX(IF(`date` IS NULL, '2299-12-31', `date`))
FROM `table`

If your result has '2299-12-31' as a date, you know it was null. Since the date is so far in the future, it will return null dates before all else.
Sign Up Login You must be logged in to post a comment.