MySQL Tutorial  /  Examples of Common Queries  /  Maximum of Column per Group

7.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.