MySQL에서 그룹별 최대값을 가진 행 조회하는 방법

MySQL에서 특정 열의 그룹별 최대값을 가진 행 찾기

MySQL을 사용하다 보면 각 그룹 내에서 특정 열의 최대값을 가진 행 전체를 조회해야 하는 경우가 자주 발생합니다. 예를 들어, 상품별로 가장 높은 가격의 데이터만 추출하거나, 부서별로 최고 연봉자의 정보를 가져오는 상황이 대표적입니다.

이때 상관 서브쿼리(Correlated Subquery)를 활용하면 원하는 결과를 손쉽게 얻을 수 있습니다.

기본 문법

SELECT colName1, colName2, colName3
FROM tableName s1
WHERE colName3 = (
    SELECT MAX(s2.colName3)
    FROM tableName s2
    WHERE s1.colName1 = s2.colName1
)
ORDER BY colName1;

이 쿼리는 외부 테이블(s1)과 내부 서브쿼리(s2)가 같은 테이블을 참조하면서, 그룹 기준 열(colName1)이 일치하는 행들 중 최대값과 동일한 값을 가진 행만 필터링합니다.

예제: PRODUCT 테이블

다음과 같은 PRODUCT 테이블이 있다고 가정해 보겠습니다.

<PRODUCT>

+---------+-----------+--------+
| Article | Warehouse | Price  |
+---------+-----------+--------+
| 1       | North     | 255.50 |
| 1       | North     | 256.05 |
| 2       | South     | 90.50  |
| 3       | East      | 120.50 |
| 3       | East      | 123.10 |
| 3       | East      | 122.10 |
+---------+-----------+--------+

위 테이블에는 Article(상품 번호)별로 여러 개의 가격 데이터가 존재합니다. 이제 각 상품별로 가장 높은 가격(Price)을 가진 행만 조회해 보겠습니다.

쿼리

SELECT Article, Warehouse, Price
FROM Product p1
WHERE Price = (
    SELECT MAX(p2.Price)
    FROM Product p2
    WHERE p1.Article = p2.Article
)
ORDER BY Article;

실행 결과

+---------+-----------+--------+
| Article | Warehouse | Price  |
+---------+-----------+--------+
| 0001    | North     | 256.05 |
| 0002    | South     | 90.50  |
| 0003    | East      | 123.10 |
+---------+-----------+--------+

쿼리 동작 원리

위 쿼리는 상관 서브쿼리 방식으로 동작합니다. 외부 쿼리의 각 행에 대해 서브쿼리가 반복 실행되며, 같은 Article 번호를 가진 행들 중에서 MAX(Price) 값을 구합니다. 그리고 외부 행의 Price가 해당 최대값과 일치할 때만 결과에 포함됩니다.

그 결과, 각 상품(Article)별로 최고 가격을 가진 단 하나의 행만 깔끔하게 추출할 수 있습니다.

참고: 윈도우 함수를 활용한 대안

MySQL 8.0 이상에서는 윈도우 함수를 사용해 더 효율적으로 처리할 수도 있습니다.

SELECT Article, Warehouse, Price
FROM (
    SELECT Article, Warehouse, Price,
           RANK() OVER (PARTITION BY Article ORDER BY Price DESC) AS rnk
    FROM Product
) ranked
WHERE rnk = 1
ORDER BY Article;

상관 서브쿼리는 데이터량이 많을 경우 성능 저하가 있을 수 있으므로, 대용량 테이블이라면 윈도우 함수 방식이나 인덱스 활용을 함께 고려하는 것이 좋습니다.