mysql - Group-wise Maximum of a Certain Column

Question

I've got the table:

SELECT * FROM shop;

+---------+--------+------
| article | dealer | price
+---------+--------+------
|    0001 | A      |  3.45
|    0001 | B      |  3.99
|    0002 | A      | 10.99
|    0003 | B      |  1.45
|    0003 | C      |  1.69
|    0003 | D      |  1.25
|    0004 | D      | 19.95
+---------+--------+------
7 rows in set (0.20 sec)

And I want to get - for each article - the dealer or dealers with the most expensive price.

Could anyone tell me why this doesn’t work?

SELECT article, dealer, MAX(price) FROM shop GROUP BY(article);

For this query, I get the following result-set;

+---------+--------+------------+
| article | dealer | MAX(price) |
+---------+--------+------------+
|    0001 | A      |       3.99 |
|    0002 | A      |      10.99 |
|    0003 | B      |       1.69 |
|    0004 | D      |      19.95 |
+---------+--------+------------+
4 rows in set (0.03 sec)

Although the max prices are correct, I got the wrong dealers for some articles.

Answer 1

According to your question it seems that you have already read the article about group-wise maximum of a certain column, however you just don't understand why the method you mentioned does not work as you expect.

Let's imagine a query like this:

SELECT article, dealer, MAX(price), MIN(price) 
FROM shop 
GROUP BY article

What value of a dealer do you expect?

I think this answers your question.