`DISTINCT ON` của PostgreSQL làm gì? So với `ROW_NUMBER()` khi lấy bản ghi mới nhất mỗi nhóm thì nên chọn cái nào?

DISTINCT ON (expr) giữ lại dòng đầu tiên của mỗi giá trị expr, theo thứ tự ORDER BY của chính truy vấn đó.

Đây là cú pháp riêng của PostgreSQL cho bài toán "lấy bản ghi mới nhất mỗi nhóm".

-- gia moi nhat cua tung san pham
SELECT DISTINCT ON (product_id)
       product_id, price, effective_at
FROM price_history
ORDER BY product_id, effective_at DESC;

Ràng buộc bắt buộc: ORDER BY phải bắt đầu bằng đúng các biểu thức trong DISTINCT ON, rồi mới đến tiêu chí chọn dòng nào.

Cách viết chuẩn SQL, chạy được ở mọi hệ:

SELECT product_id, price, effective_at
FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY product_id ORDER BY effective_at DESC
  ) AS rn
  FROM price_history
) t
WHERE rn = 1;

Chọn thế nào:
- DISTINCT ON: ngắn hơn, và với index (product_id, effective_at DESC) thì PostgreSQL thường dùng index scan bỏ qua dòng thừa — nhanh hơn.
- ROW_NUMBER(): chuẩn SQL, di động sang MySQL 8 / SQL Server, và linh hoạt hơn khi cần top-N (rn <= 3) chứ không chỉ top-1.
- Nếu chỉ cần cột dùng để so sánh (vd max ngày) thì GROUP BY product_id, MAX(effective_at) là đủ; DISTINCT ON/ROW_NUMBER cần thiết khi phải kéo theo các cột khác của đúng dòng đó.