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.

Answer 2

Standard SQL would reject your query because you can not SELECT non-aggregate fields that are not part of the GROUP BY clause in an aggregate query.

You're using a MySQL extension of SQL described here:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Answer 3

I just tumbled over this question and wonder why noone comes to idea to join the table with itself as described in certain tutorials (see links below).

So I'd suggest the following solution:

Select A.* 
From      shop As A 
Left Join shop As B On  A.article
                     =  B.Article 
                    AND A.price 
                      < B.price 
Where B.price Is Null;

The magic is obvious: join the table with itself and link any records in it to any other record having a higher price. From those, grab only those having NO linked record with a higher price (for these records are the ones with the highest price).

As far as I have experienced, this solution is even the best regarding its performance.

This part of the MySQL documentation and/or this very interesting article by Jan Kneschke might be helpful — enjoy!

Answer 4

This does not work, because if you use group by, you can not use the individual fields of the original rows (except for the field you are grouping on). The correct way to do this, is to make an inner/nested query to select the dealer, suck as this (I haven't tested it, so it might be slightly off):

SELECT article, MAX(price) as maxPrice, (SELECT dealer FROM shop AS s2 WHERE s2.article = s1.article AND s2.price = maxPrice) AS expensiveDealer FROM shop AS s1 GROUP BY(article);

Answer 5

Here you go:

SELECT article, dealer, price
FROM (SELECT article, dealer, price
      FROM shop
      ORDER BY price DESC) AS h
GROUP BY article

This solution doesn't even require a MAX() function. :)

Note: This solution doesn't work with ONLY_FULL_GROUP_BY active and only works in MySQL. This solution is to a certain extent unsupported due to lack of documentation confirming this behavior. It works well for me and has always worked well for me however.

This method still works on the latest MySQL on sqlfiddle.