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.