Using User-Defined Variables 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. +---------+--------+-------+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---------+--------+-------+ +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+ mysql>  SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql>  SELECT * FROM shop WHERE price=@min_price OR price=@max_price; 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; 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; 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; Uncorrelated subquery: LEFT JOIN : 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” . Common table expression with window function: 3.6.5 Using User-Defined Variables 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: 330