MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.6.3 グループごとのカラムの最大値

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / グループごとのカラムの最大値

3.6.3 グループごとのカラムの最大値

タスク: 物品ごとの最高値を調べます。

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

PREV HOME UP NEXT

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.