content_image/813192.jpg content_image/813193.jpg
<img src='content_image/813192.jpg'>

The preceding example uses a correlated subquery, which can be inefficient (see Section 13.2.11.7, “Correlated Subqueries” ). Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM  clause, a LEFT JOIN , or a common table expression with a window function. 

## Uncorrelated subquery: 

SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article; 

## LEFT JOIN : 

SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article; 

The LEFT JOIN  works on the basis that when s1.price  is at its maximum value, there is no s2.price with a greater value and thus the corresponding s2.article  value is NULL . See Section 13.2.10.2, “JOIN Clause” . 

## 3.6.5 Using User-Defined Variables 

## Common table expression with window function: 

WITH s1 AS ( SELECT article, dealer, price, RANK() OVER (PARTITION BY article ORDER BY price DESC ) AS ‘Rank‘ FROM shop ) SELECT article, dealer, price FROM s1 WHERE ‘Rank‘ = 1 ORDER BY article; 

You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See Section 9.4, “User-Defined Variables” .) 

For example, to find the articles with the highest and lowest price you can do this: 

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; 

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

