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, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
Another solution is to sort all rows descending by price and get
only the first row using the MySQL-specific
LIMIT clause:
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.
