MySQL :The Row Holding the Maximum of a Certain Column

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

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.