GROUP BY và MAX trên nhiều cột trong MySQL

Tóm tắt

Tìm hàng có Value1 lớn nhất trong mỗi CategoryId, sau đó lấy MAX(Value2) của các hàng đó. Yêu cầu subquery vì không thể dùng aggregate lồng nhau trực tiếp.

Ví dụ

1. Tạo bảng

CREATE TABLE GroupByMaxDemo (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    CategoryId INT,
    Value1 INT,
    Value2 INT
);

2. Dữ liệu

INSERT INTO GroupByMaxDemo (CategoryId, Value1, Value2) VALUES
(10, 100, 50),
(10, 100, 70),
(10, 50,  100),
(20, 180, 150);

3. Truy vấn

SELECT tbl2.CategoryId, tbl2.Value1, MAX(tbl2.Value2)
FROM (
    SELECT CategoryId, MAX(Value1) AS Value1
    FROM GroupByMaxDemo
    GROUP BY CategoryId
) tbl1
INNER JOIN GroupByMaxDemo tbl2 
  ON tbl2.CategoryId = tbl1.CategoryId 
  AND tbl2.Value1 = tbl1.Value1
GROUP BY tbl2.CategoryId, tbl2.Value1;

4. Kết quả

+------------+--------+------------------+
| CategoryId | Value1 | max(tbl2.Value2) |
+------------+--------+------------------+
| 10         | 100    | 70               |
| 20         | 180    | 150              |
+------------+--------+------------------+

Giải thích

1. Subquery tbl1: Tìm MAX(Value1) cho mỗi CategoryId → (10,100), (20,180).
2. Join lại bảng gốc để lấy các hàng có Value1 = max đó.
3. Group lại và lấy MAX(Value2) của tập con đó.

Cách thay thế (MySQL 8.0+)

WITH ranked AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY Value1 DESC, Value2 DESC) AS rn
    FROM GroupByMaxDemo
)
SELECT CategoryId, Value1, Value2
FROM ranked
WHERE rn = 1;

Window function ngắn gọn, dễ hiểu, hiệu năng tốt hơn.