3.5.3 Maximum of Column per Group

``What's 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

If you try to use the following query, "SELECT article, dealer, MAX(price) AS price FROM shop GROUP BY article". You won't get the right dealer, that is kindof strange in my opinion ...

SELECT col_id,max(col1),col2 FROM tbl GROUP BY col_id

will not return the correct value of col2, queries with aggregation functions will only return the aggregated values and the column grouped by correctly.. E.g. you may include a MAX(col1) and a MIN(col2) and a AVG(col3) in the same query and then it does not make sense to have none-aggregated values. If you need to find a max or min record you should use the sample for such in the MySQL docs using a subquery, or if you are using a pre-4.1 version use two queries.

Other DBMS give an error if you do such a query, it would be helpful if MySQL did that as well (Feature request :) peek in pgsql)

Add your own comment.