Task: For each article, find the dealer or dealers with the most expensive price.
In standard SQL (and as of MySQL 4.1), the problem can be solved with a subquery like this:
SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);
The preceding example uses a correlated subquery, which can be
inefficient (see Section 126.96.36.199, “Correlated Subqueries”). Other
possibilities for solving the problem are to use an uncorrelated
subquery in the
FROM clause or a
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; 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;
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 the
s2 rows values will be
NULL. See Section 188.8.131.52, “JOIN Syntax”.
Before MySQL 4.1, subqueries are unavailable. Another approach is to solve the problem in several steps:
Get the list of (article,maxprice) pairs.
For each article, get the corresponding rows that have the stored maximum price.
This can easily be done with a temporary table and a join:
CREATE TEMPORARY TABLE tmp ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL); LOCK TABLES shop READ; INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article; SELECT shop.article, dealer, shop.price FROM shop, tmp WHERE shop.article=tmp.article AND shop.price=tmp.price; UNLOCK TABLES; DROP TABLE tmp;
If you don't use a
TEMPORARY table, you must
also lock the
“Can it be done with a single query?”
Yes, but only by using a quite inefficient trick called the “MAX-CONCAT trick”:
SELECT article, SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer, 0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price FROM shop GROUP BY article; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---------+--------+-------+
The last example can be made a bit more efficient by doing the splitting of the concatenated column in the client.