Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.8Mb
PDF (A4) - 37.8Mb
PDF (RPM) - 36.4Mb
HTML Download (TGZ) - 9.8Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.6Mb
Man Pages (TGZ) - 209.5Kb
Man Pages (Zip) - 318.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
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

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.