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

### 4.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);

+---------+--------+-------+
| 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 14.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;
```

`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;
```

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 the `s2` rows values will be `NULL`. See Section 14.2.9.2, “JOIN Syntax”.

Posted by Kasey Speakman on October 3, 2009
The examples here by MySQL are not that helpful if you are working with temporary tables and need to add conditions to the query... like prices only from this year, for instance.

The first example falls down with temporary tables because you can't select from the same temp table twice in the same query (get "can't reopen table" error). It also doesn't give you just distinct articles, if that's what you were after. It will show the same article twice in the case where you have 2 articles from different dealers for the same price.

The second example is both difficult to understand and cumbersome to manage if you have extra query conditions (i.e. don't want to select the whole table). With large tables, you will end up needing to repeat query conditions in inner and outer queries (or at least in the LEFT JOIN ON part) for query speed.

I found another method for group-wise max that is a bit more straightforward and less duplicating of conditions:

SELECT *
FROM (SELECT *
FROM shop
[WHERE conditions]
ORDER BY price DESC) AS s
GROUP BY article

The inner query orders all records by highest price. The outer query uses a group by, which simply grabs the first distinct article that it finds. In this case the first one it finds will be the highest one since we ordered them that way.

This query is probably not as efficient as others, but it's straightforward, and it's easier on query developers when they need to use temp tables or needed to query on less than an entire table. It's only slightly less efficient than the queries given by MySQL on this page, in my tests.
Posted by Robin Palotai on October 17, 2009
Speakman: Your solution sounds good, but is it stable?
The manual tells that non-grouped fields have an indeterminate value if not all values of rows are the same.

Right now the value at the first encountered row may be used, but may it happen in the future releases that other random row will be selected? It would be nice to hear an insider's opinion.
Posted by O T on November 19, 2009
I suggest the following syntax which orders by column1 as usual but returns the corresponding column2 when given:

max( column1 [, column2 ] )
Posted by Rick James on December 7, 2009
Speakman's solution ("group by trick", as I call it) is stable and far more efficient than the methods given in the article.

This is Order(N*logN); the article is Order(N*N). That is, as the number of rows grows, the article's methods slow down quadratically -- a million rows would require a trillion operations.
Posted by Alexandru Trandafir Catalin on April 21, 2010
I think of an easy way to get the row with the greatest value for one field, I don't know if it is efficient but it's sure very easy:

SELECT * FROM PRODUCTS ORDER BY PRICE DESC LIMIT 0,1

And that's all, it will give you the product with the biggest price.
Posted by Charlie Chambers on July 13, 2010
Alexandru Trandafir Catalin:
The query you provide is finding the greatest price for any article, i.e. a single value.
The problem that the page is targeted toward is finding the greatest price for each article, i.e. one value per article in the table.

Posted by Francisco Tirado on December 30, 2010
O T: Although that would be awesome, it did not work for me and I couldn't find it in the manual.
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.

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

was faster than

SELECT iA.tid
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:

SELECT * FROM shop
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 : http://dev.mysql.com/doc/refman/5.1/en/example-maximum-row.html

"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 (https://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html) 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. http://bugs.mysql.com/71942
Posted by Alex Wilding on December 29, 2015
The example given by Kasey Speakman appears to fail when you change it to try to find the minimum prices by replacing "desc" with "asc". i.e;

SELECT *
FROM (SELECT *
FROM shop
ORDER BY price ASC) AS s
GROUP BY article

does not produce the output you may expect.
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.)