Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 294.0Kb
Man Pages (Zip) - 409.0Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb
Excerpts from this Manual

MySQL 8.3 Reference Manual  /  ...  /  The Row Holding the Maximum of a Certain Column

5.6.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:

Press CTRL+C to copy
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0004 | D | 19.95 | +---------+--------+-------+

Another solution is to use a LEFT JOIN, as shown here:

Press CTRL+C to copy
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;

You can also do this by sorting all rows descending by price and get only the first row using the MySQL-specific LIMIT clause, like this:

Press CTRL+C to copy
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.