content_image/723397.jpg content_image/723403.jpg content_image/723398.jpg content_image/723404.jpg content_image/723399.jpg
This is easily done with a subquery: 

SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop); 

<img src='content_image/723397.jpg'>

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: 

## 3.6.3 Maximum of Column per Group 

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; 

<img src='content_image/723403.jpg'>

Task: Find the highest price per article. 

SELECT article, MAX(price) AS price FROM shop GROUP BY article ORDER BY article; 

<img src='content_image/723398.jpg'>

<img src='content_image/723404.jpg'>

## Note 

If there were several most expensive articles, each with a price of 19.95, the LIMIT solution would show only one of them. 

## 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column 

Task: For each article, find the dealer or dealers with the most expensive price. 

This problem can be solved with a subquery like this one: 

SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) ORDER BY article; 

<img src='content_image/723399.jpg'>

