MySQL Tutorial  /  Examples of Common Queries  /  The Row Holding the Maximum of a Certain Column

7.2 The Row Holding the Maximum of a Certain Column

Task: Find the number, dealer, and price of the most expensive article.

This is easily done with a subquery:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

Other solutions are to use a LEFT JOIN or to sort all rows descending by price and get only the first row using the MySQL-specific LIMIT clause:

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
Note

If there were several most expensive articles, each with a price of 19.95, the LIMIT solution would show only one of them.


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 Mike Ethetton on April 6, 2016
The first example given in the article would force a full-table scan, which can be expensive for large tables (no way to index it to prevent this)

The second example could work with an index, but does not help if you need to retrieve multiple records, for example the row with the latest comment for each user.

Here is an example which can work in this case:

SELECT C.*
FROM comments C
INNER JOIN (
SELECT userID, MAX( commentTime ) AS commentTime
FROM comments
GROUP BY userID
) AS C2 ON C.userID = C2.userID AND C.commentTime=C2.commentTime

In this example, you would create a compound index on userID & commentTime.