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

SELECT article, dealer, priceFROM   shopWHERE  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.priceFROM shop s1LEFT JOIN shop s2 ON s1.price < s2.priceWHERE s2.article IS NULL;SELECT article, dealer, priceFROM shopORDER BY price DESCLIMIT 1;