Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.9Mb
PDF (A4) - 44.9Mb
PDF (RPM) - 40.6Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.6Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 205.8Kb
Man Pages (Zip) - 308.3Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 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, “Correlated Subqueries”). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause, a LEFT JOIN, or a common table expression with a window function.

Uncorrelated subquery:

SELECT s1.article, dealer, s1.price
FROM shop s1
  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;


SELECT s1.article,, 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, “JOIN Syntax”.

Common table expression with window function:

WITH s1 AS (
   SELECT article, dealer, price,
          RANK() OVER (PARTITION BY article
                           ORDER BY price DESC
                      ) AS `Rank`
     FROM shop
SELECT article, dealer, price
  FROM s1
  WHERE `Rank` = 1
ORDER BY article;

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 Deon Kuhn on February 9, 2012
In my particular case I was only working with one record, and the correlated sub query solution proved 50% faster than the 'group by' trick. I am getting the most recent record to be altered by user 6 by way of a main table and changes log.

FROM main_table iA
JOIN log_table iC ON iA.tid = iC.tid AND iC.userid = 6
WHERE iC.status = 1 AND = ( SELECT MAX( id ) `scmax` FROM log_table WHERE iA.transactionID = trxid )

was faster than

FROM main_table iA
JOIN ( SELECT id, tid, userID FROM ( SELECT id, tid, userID FROM log_table WHERE userID = 6 ORDER BY id DESC ) iiA GROUP BY tid ) iC ON iA.tid = iC.tid AND iC.status = 1

  Posted by Daniel Bermudez on November 7, 2012
I am using this as a tutorial and tried doing it by myself before seeing the "answer" and I came up with this:

WHERE price IN (SELECT MAX(price)
FROM shop GROUP BY article);

I think is very straightforward and easy to understand but I might be missing something.

Why nobody recommended this?
  Posted by Thibault Delor on November 15, 2012
@Daniel Bermudez :
You can find your query there :

"Why nobody recommended this?" :
1) it doesn't do what we want there
2) it is slow
  Posted by Predrag Bradaric on January 25, 2013
I agree with Robin Palotai. Example given by Kasey Speakman is "unreliable" in the long run.
As stated in MySQL reference manual ( values chosen by GROUP BY extension are indeterminate - it is not "guaranteed" that first row (top->down) with distinct value will be chosen in the final result.
Granted, tests have shown that (at the moment) MySQL server will choose first row (top->down) that it encounters but that can break in any future update.
  Posted by John Pratt on March 5, 2014
Perhaps a future version of MySQL will no longer be "indeterminate". It would be easier to write this query if the Select would reliably always select the top row.
Sign Up Login You must be logged in to post a comment.