Skip navigation links
MySQL 5.1 Reference Manual :: 3 Tutorial :: 3.6 Examples of Common Queries :: 3.6.3 Maximum of Column per Group
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column »
Section Navigation      [Toggle]

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 |
+---------+-------+
Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Marco Gergele on September 30 2004 10:24am[Delete] [Edit]

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 1:41pm[Delete] [Edit]

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

Add your own comment.