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

5.6.4 특정 Column에 대해 Group-wise Maximum을 갖는 Row들

작업: 각 article마다 가장 비싼 price를 가진 dealer들을 찾으시오.

이 문제는 다음과 같은 subquery를 사용하여 해결할 수 있습니다:

1SELECT article, dealer, price
2FROM   shop s1
3WHERE  price=(SELECT MAX(s2.price)
4              FROM shop s2
5              WHERE s1.article = s2.article)
6ORDER BY article;
7
8+---------+--------+-------+
9| article | dealer | price |
10+---------+--------+-------+
11|    0001 | B      |  3.99 |
12|    0002 | A      | 10.99 |
13|    0003 | C      |  1.69 |
14|    0004 | D      | 19.95 |
15+---------+--------+-------+

위 예제는 상관 서브쿼리를 사용하며, 이는 비효율적일 수 있습니다( Section 15.2.15.7, “Correlated Subqueries” 참고). 이 문제를 해결하는 다른 방법으로는 FROM 절에서 비상관 서브쿼리를 사용하거나, LEFT JOIN, 또는 윈도우 함수를 사용하는 공통 테이블 익스프레션을 사용하는 것이 있습니다.

비상관 서브쿼리:

1SELECT s1.article, dealer, s1.price
2FROM shop s1
3JOIN (
4  SELECT article, MAX(price) AS price
5  FROM shop
6  GROUP BY article) AS s2
7  ON s1.article = s2.article AND s1.price = s2.price
8ORDER BY article;

LEFT JOIN:

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

LEFT JOIN은 s1.price가 최대값일 때, 그보다 큰 값을 갖는 s2.price가 존재하지 않으므로, 해당하는 s2.article 값이 NULL이 된다는 점을 기반으로 동작합니다. Section 15.2.13.2, “JOIN Clause”를 참고하십시오.

윈도우 함수를 사용하는 공통 테이블 익스프레션:

1WITH s1 AS (
2   SELECT article, dealer, price,
3          RANK() OVER (PARTITION BY article
4                           ORDER BY price DESC
5                      ) AS `Rank`
6     FROM shop
7)
8SELECT article, dealer, price
9  FROM s1
10  WHERE `Rank` = 1
11ORDER BY article;