Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.5Mb
PDF (A4) - 39.5Mb
PDF (RPM) - 38.7Mb
HTML Download (TGZ) - 11.0Mb
HTML Download (Zip) - 11.0Mb
HTML Download (RPM) - 9.7Mb
Man Pages (TGZ) - 217.2Kb
Man Pages (Zip) - 326.9Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  The Rows Holding the Group-wise Maximum of a Certain Column

3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column

Task: For each article, find the dealer or dealers with the most expensive price.

This problem can be solved with a subquery like this one:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article)
ORDER BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

The preceding example uses a correlated subquery, which can be inefficient (see Section 13.2.10.7, “Correlated Subqueries”). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause or a LEFT JOIN.

Uncorrelated subquery:

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUP BY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;

LEFT JOIN:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and thus the corresponding s2.article value is NULL. See Section 13.2.9.2, “JOIN Syntax”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Paul Macey on January 4, 2017
The solution suggestion by Kasey Speakman worked for me with a table of ~20 million rows and 7 columns - took 2+ minutes to run. However, the JOIN & LEFT JOIN suggestions ran for hours without completing.
  Posted by Andras Toth on January 7, 2017
The latest release doesn't seem to support the "GROUP BY" trick anymore, which is a performance issue (see earlier comments on slower JOIN queries) but also means the need of far more complex queries. Consider this:

SELECT *
FROM
(SELECT *
FROM table
ORDER BY date DESC,id DESC) t
GROUP BY t.item;

This query results a table in which you will find a single row for every item with the latest date, but for those items which have multiple rows with the latest date, it provides you the one with the highest id value.

With "GROUP BY" trick adding a secondary group-wise maximum was so easy, you had to simply extend your ORDER BY clause with another ranking option.

With the JOIN solutions mentioned in the manual, the only way to do this, is to create an inner query for the primary maximum then using an outer query for the secondary maximum.

SELECT t12.item,t12.price,t12.date,t12.id
FROM
(SELECT t1.item,t1.price,t1.date,t1.id
FROM table t1
LEFT JOIN table t2 ON t1.item=t2.item and t1.date<t2.date
WHERE t2.date is NULL) t12
LEFT JOIN table t3 ON t12.item = t3.item AND t12.date=t3.date AND t12.id<t3.id
WHERE t3.id is NULL.

Now compare it with the GROUP BY trick. MAX() based solutions are probably even more complicated. MySQL should really work out a more sophisticated solution for this. (I could mention other SQL DBMSs.)
  Posted by Richard JI on January 21, 2017
The query below is similar to "Uncorrelated subquery" but is more straightforward -

select * from shop
where (article, price) in
(select article, max(price) from shop group by article);
  Posted by Rick James on May 24, 2018
For scaling... Beware of some techniques; they are Order(N) or even Order(N*N), where N is the number of input rows. The Order(N*N) are usually recognizable by "... LEFT JOIN... a.id < b.id ..."

This link gives two techniques that scale better: http://mysql.rjweb.org/doc.php/groupwise_max

It also provide efficient code for "Top-N in each Group".