MYSQL에서 최대값을 갖는 모든 행을 선택합니다.

질문:

시각 형상 테이블
item     title    numTimesPurchased
1        Beer     30
2        Chips    13
3        Smokes   30
4        Gum      3
지금까지 제가 쓴 코드.
SELECT Purchase.item, Item.title, SUM(quantity) AS numTimesPurchased 
FROM Item 
INNER JOIN Purchase ON Item.id = Purchase.item 
GROUP BY item, title;
줄/여러 줄의 모든 열을 선택해야 합니다. 최대 값은numTimePurchased입니다.따라서 이 표에서 항목 1과 3을 동시에 선택해야 한다.
ORDER BY와 LIMIT를 사용하여 데이터를 정렬해 보았지만, 여러 줄에 최대 값을 가진 데이터 집합에는 적용되지 않습니다. 이 문제를 해결할 다른 방법이 있습니까?

답안

MySQL 8+에서 이 문제를 처리하는 좋은 방법 중 하나는 RANK 분석 함수를 사용하는 것이다.
WITH cte AS (
    SELECT p.item, i.title, SUM(quantity) AS numTimesPurchased,
           RANK() OVER (ORDER BY SUM(quantity) DESC) rnk
    FROM Item i
    INNER JOIN Purchase p ON i.id = p.item
    GROUP BY p.item, i.title
)

SELECT item, title, numTimesPurchased
FROM cte
WHERE rnk = 1;
이전 버전의 MySQL에서 이 문제를 처리하는 방법 중 하나는 HAVING 자문에서 관련이 없는 하위 조회를 사용하여 구매 횟수를 검사하는 것이다.
SELECT p.item, i.title, SUM(quantity) AS numTimesPurchased
FROM Item i
INNER JOIN Purchase p ON i.id = p.item
GROUP BY p.item, i.title
HAVING SUM(quantity) = (SELECT SUM(quantity)
                        FROM Item i
                        INNER JOIN Purchase p ON i.id = p.item
                        GROUP BY p.item, i.title
                        ORDER BY SUM(quantity) DESC
                        LIMIT 1);