ROW_NUMBER

Bạn sẽ học được

• ROW_NUMBER() đánh số dòng trong cửa sổ.
• Kết hợp PARTITION BY để đánh số lại trong từng nhóm.
• Lấy dòng đầu tiên của mỗi nhóm — mẫu "top N mỗi nhóm".
• Loại bỏ trùng lặp bằng ROW_NUMBER.
• Vì sao thứ tự khi đồng hạng là không xác định.

Khái niệm cốt lõi

ROW_NUMBER() trả về số thứ tự 1, 2, 3… cho các dòng trong cửa sổ, theo thứ tự bạn chỉ định. Khác với RANK, nó luôn cấp số duy nhất, kể cả khi các dòng bằng nhau ở cột sắp xếp.

SELECT category, name, price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
ORDER BY category, rn;

Kết quả đánh số lại từ 1 trong từng danh mục:

Hai yếu tố tạo nên con số: PARTITION BY quyết định phạm vi đánh số, ORDER BY quyết định ai đứng trước. Đổi một trong hai, kết quả đổi.

Mẫu quan trọng nhất của ROW_NUMBER là top N mỗi nhóm: lấy sản phẩm đắt nhất của mỗi danh mục, đơn đầu tiên của mỗi khách, bản ghi mới nhất của mỗi thiết bị. Cấu trúc luôn gồm hai bước: đánh số trong CTE, lọc rn = 1 ở ngoài.

WITH ranked AS (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
)
SELECT category, name, price
FROM ranked
WHERE rn = 1
ORDER BY price DESC;

Bốn dòng: Nồi chiên không dầu (1.690.000), Bàn phím cơ (1.250.000), Áo thun cotton (250.000), Sách SQL thực chiến (180.000).

Không thể viết WHERE ROW_NUMBER() OVER (...) = 1 trực tiếp vì WHERE chạy trước SELECT — đúng như bài trước đã giải thích. CTE không phải mẹo vặt mà là bắt buộc về mặt ngữ nghĩa.

Công dụng thứ hai: khử trùng lặp. Khi một bảng có nhiều bản ghi cho cùng một thực thể (lịch sử cập nhật, nhiều lần nhập liệu), ROW_NUMBER giúp chọn đúng một bản ghi theo quy tắc:

WITH ranked AS (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY id) AS rn
  FROM orders o
)
SELECT customer_id, id AS don_dau_tien
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

Kết quả 11 dòng — mỗi khách có đơn một lần, với id đơn nhỏ nhất. Hà Bùi không xuất hiện vì chưa có đơn. Bình Trần có đơn đầu là đơn 2 (ba đơn của Bình là 2, 6, 12).

Điểm phải nhớ: khi các dòng bằng nhau ở tất cả cột trong ORDER BY, ROW_NUMBER vẫn cấp số khác nhau nhưng không đảm bảo ai trước ai. PostgreSQL có thể trả thứ tự khác nhau giữa các lần chạy, đặc biệt khi có song song hoá hoặc dữ liệu đổi. Nếu cần xác định, thêm cột phụ vào ORDER BY:

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC)

Thêm id làm tiêu chí phá hoà — quy tắc đơn giản nhưng loại bỏ hoàn toàn sự ngẫu nhiên.

So sánh nhanh với RANK trên cùng dữ liệu đếm đơn của khách:

WITH counts AS (
  SELECT c.id, c.name, COUNT(o.id) AS n
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.name
)
SELECT name, n,
       ROW_NUMBER() OVER (ORDER BY n DESC) AS rn,
       RANK() OVER (ORDER BY n DESC) AS r
FROM counts
ORDER BY n DESC, name;

Bình Trần (3 đơn) có rn = 1, r = 1. Ba khách 2 đơn nhận rn 2, 3, 4 nhưng cùng r = 2. Bảy khách 1 đơn nhận rn từ 5 đến 11 nhưng cùng r = 5. Bài sau sẽ mổ xẻ sự khác biệt này.

Ví dụ chạy được

Top 2 sản phẩm đắt nhất mỗi danh mục:

WITH ranked AS (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
)
SELECT category, name, price, rn
FROM ranked
WHERE rn <= 2
ORDER BY category, rn;

Sáu dòng: Gia dụng có Nồi chiên và Giá kê; Điện tử có Bàn phím và Tai nghe; Sách và Thời trang mỗi danh mục chỉ một sản phẩm nên chỉ một dòng. Đây là mẫu dùng cho báo cáo "top sản phẩm theo nhóm" — thay products bằng bảng bán hàng đã gom nhóm là ra báo cáo thật.

Đơn hàng lớn nhất của mỗi khách:

WITH totals AS (
  SELECT o.id, o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.id, o.customer_id
), ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM totals
)
SELECT customer_id, id, total
FROM ranked
WHERE rn = 1
ORDER BY total DESC
LIMIT 3;

Ba dòng đầu: khách của đơn 13 với 4.030.000; khách của đơn 7 với 3.960.000; khách của đơn 6 với 2.950.000. Mỗi khách chỉ xuất hiện một lần dù có nhiều đơn. Nếu dùng DISTINCT trên customer_id bạn sẽ mất cột total; ROW_NUMBER giữ được cả hai.

Khử trùng lặp có kiểm soát — chọn bản ghi mới nhất mỗi khách:

WITH ranked AS (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) AS rn
  FROM orders o
)
SELECT customer_id, id, order_date, status
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

Mười một dòng, mỗi khách một đơn gần nhất. Tiêu chí phá hoà id DESC đảm bảo nếu hai đơn cùng ngày, đơn có id lớn hơn (tạo sau) được chọn. Quy tắc này nên được viết rõ trong tài liệu của báo cáo, vì người đọc cần biết "mới nhất" được định nghĩa thế nào.

Phân trang ổn định:

WITH numbered AS (
  SELECT id, name, price,
         ROW_NUMBER() OVER (ORDER BY price DESC, id) AS rn
  FROM products
)
SELECT id, name, price
FROM numbered
WHERE rn BETWEEN 4 AND 6
ORDER BY rn;

Ba sản phẩm hạng 4–6: Chuột không dây 450.000, Giá kê laptop 390.000, Bình giữ nhiệt 320.000. Cách phân trang này vẫn phải tính toàn bộ ROW_NUMBER trước khi lọc, nên với bảng lớn nó không nhanh hơn OFFSET — chương hiệu năng sẽ bàn cách khác.

Thực hành

1. Đánh số sản phẩm theo giá giảm dần trong từng danh mục. Kiểm tra Gia dụng có 4 hạng, Điện tử 3 hạng.
2. Lấy sản phẩm đắt nhất mỗi danh mục. Đáp số: Nồi chiên, Bàn phím, Áo thun, Sách SQL.
3. Lấy đơn đầu tiên của mỗi khách theo id. Đáp số 11 dòng; Bình Trần là đơn 2.
4. Chạy câu so sánh ROW_NUMBER và RANK ở trên, ghi lại bốn giá trị r = 2 và bảy giá trị r = 5.
5. Thêm khách trùng tên giả lập vào một bản sao dữ liệu và xác nhận PARTITION BY customer_id không gộp họ.

Lỗi thường gặp

Lỗi cuối cùng hay gặp khi xuất báo cáo: cửa sổ sắp theo giá nhưng câu chính sắp theo tên, nên rn nhảy lung tung trên màn hình. Số vẫn đúng — chỉ là thứ tự hiển thị khác thứ tự đánh số. Hãy sắp ngoài theo chính cột rn khi muốn người đọc thấy đúng hạng.

Tóm tắt

• ROW_NUMBER() cấp số duy nhất 1..n trong cửa sổ.
• PARTITION BY đánh số lại theo nhóm; ORDER BY quyết định thứ tự.
• Mẫu "top N mỗi nhóm": đánh số trong CTE, lọc rn <= n ở ngoài.
• Dùng để khử trùng lặp khi cần giữ một bản ghi đại diện.
• Đồng hạng không xác định — luôn thêm tiêu chí phá hoà.