Trong SQL, mệnh đề ORDER BY quyết định thứ tự các dòng trả về. Nhiều người quen viết ORDER BY created_at DESC và dừng lại ở đó. Bài này gom nhóm một họ pattern quan trọng hơn: sắp xếp theo khóa scalar — tức mỗi dòng được gán một giá trị đơn (số, chuỗi, boolean, timestamp, v.v.) làm tiêu chí so sánh, và giá trị đó có thể đến từ biểu thức chứ không nhất thiết là một cột vật lý trong bảng.

Ở đây scalar (vô hướng / đơn trị) nghĩa là “một giá trị nguyên tử”, đối lập với kiểu phức hợp như một hàng đầy đủ hay một tập kết quả; trong ORDER BY, mỗi khóa sort phải là biểu thức cho ra đúng một giá trị kiểu SQL trên một dòng để so sánh được với dòng khác.

PostgreSQL làm việc này rất thoải mái; hiểu rõ giúp bạn viết query linh hoạt hơn và tránh vài bẫy hiệu năng.

Scalar sort là gì (ở góc nhìn thực dụng)

Chuẩn SQL coi các sort key trong ORDER BY là các scalar expression: mỗi dòng của kết quả trung gian (sau FROM / WHERE / GROUP BY / HAVING) được đánh giá biểu thức một lần, rồi so sánh theo quy tắc kiểu dữ liệu và collation.

Ví dụ tất cả đều là scalar sort:

-- Cột (trivial)
SELECT id, name FROM users ORDER BY name;

-- Hàm trên cột
SELECT id, email FROM users ORDER BY lower(email);

-- CASE (một giá trị mỗi dòng)
SELECT id, status FROM orders
ORDER BY
  CASE status
    WHEN 'paid' THEN 1
    WHEN 'pending' THEN 2
    ELSE 3
  END;

-- Biểu thức số học
SELECT id, price, qty FROM line_items ORDER BY price * qty DESC;

Điểm chung: một hàng → một giá trị khóa sắp xếp (hoặc nhiều khóa theo thứ tự từ trái sang phải).

Scalar subquery trong ORDER BY

PostgreSQL cho phép subquery trả về đúng một giá trị một cột (scalar subquery) trong ORDER BY. Đây là dạng “sort theo dữ liệu liên quan” mà không cần join rõ ràng trong SELECT list:

SELECT p.id, p.title
FROM posts p
ORDER BY (
  SELECT count(*)::bigint
  FROM comments c
  WHERE c.post_id = p.id
) DESC;

Lưu ý thực tế:

  • Mỗi dòng ở ngoài có thể kích hoạt đánh giá subquery (tùy planner); với bảng lớn, pattern này dễ tốn kém nếu không có index phù hợp hoặc không viết lại bằng join / aggregate.
  • Scalar subquery trong ORDER BY phải thực sự scalar: nếu trả nhiều hơn một dòng hoặc nhiều cột, PostgreSQL báo lỗi.

NULL và thứ tự so sánh trong PostgreSQL

Giá trị NULL không bằng bất kỳ giá trị nào, kể cả NULL khác, nên thứ tự khi sort theo cột nullable cần chỉ định rõ.

Từ PostgreSQL (và SQL hiện đại), dùng NULLS FIRST / NULLS LAST trên từng khóa:

SELECT id, ended_at
FROM jobs
ORDER BY ended_at DESC NULLS LAST;

Nếu không ghi, mặc định phụ thuộc hướng sort (ASC vs DESC) và phiên bản / tài liệu bạn đang đọc — tốt nhất là luôn ghi rõ khi NULL có ý nghĩa nghiệp vụ (ví dụ “chưa xong” vs “đã xong”).

Collation: scalar vẫn là scalar, nhưng quy tắc so sánh đổi

Chuỗi là scalar, nhưng thứ tự không chỉ là “byte order”. PostgreSQL dùng collation (mặc định của cột / database / hoặc chỉ định trong query):

SELECT word FROM glossary
ORDER BY word COLLATE "vi-x-icu";

Khi debug “sao sort ra khác tool kia”, hay kiểm tra collation và locale — đặc biệt với tiếng Việt có dấu, chữ hoa/thường, và các quy tắc Unicode.

Nhiều khóa: từ trái sang phải, deterministic khi cần

ORDER BY a, b, c nghĩa là: so sánh a; nếu hòa thì b; nếu hòa thì c. Nếu vẫn hòa, PostgreSQL không đảm bảo thứ tự ổn định giữa các lần chạy trừ khi bạn thêm khóa duy nhất (thường là khóa chính):

SELECT id, score FROM players ORDER BY score DESC, id ASC;

Điều này quan trọng cho pagination (LIMIT/OFFSET hoặc keyset) để không bị “nhảy trang” khi có nhiều dòng cùng score.

Hiệu năng: index trên biểu thức (functional / expression index)

Sort theo lower(email) hoặc date_trunc('day', created_at) thường không dùng được B-tree index thường trên cột gốc, trừ khi bạn tạo index khớp biểu thức:

CREATE INDEX ON users (lower(email));

SELECT * FROM users ORDER BY lower(email);

PostgreSQL có thể dùng index đó cho sort và filter tương thích. Ngược lại, sort theo biểu thức phức tạp không có index tương ứng thường dẫn tới sort trên disk hoặc trong bộ nhớ trên tập kết quả lớn — đo bằng EXPLAIN (ANALYZE, BUFFERS) trước khi đưa vào production.

Liên hệ nhanh: DISTINCT ON phụ thuộc ORDER BY

Trong PostgreSQL, DISTINCT ON (expression) yêu cầu ORDER BY bắt đầu bằng các biểu thức tương thích — về bản chất bạn đang chọn “hàng đầu tiên theo thứ tự” trong mỗi nhóm. Đây cũng là nơi scalar sort (nhiều khóa, thứ tự rõ ràng) đóng vai trò trực tiếp.

Tóm lại

  • Scalar sort = sắp xếp theo các sort key là biểu thức cho một giá trị / hàng; có thể là cột, hàm, CASE, phép toán, hoặc scalar subquery.
  • PostgreSQL mạnh ở chỗ cho phép biểu thức phong phú trong ORDER BY, kèm NULLS FIRST|LAST và COLLATE kiểm soát được.
  • Muốn nhanh và dự đoán được: thêm khóa ổn định (ví dụ id), và căn index với đúng biểu thức bạn dùng khi sort.

Nếu bạn đang tối ưu một query cụ thể, mang EXPLAIN (ANALYZE, BUFFERS) và định nghĩa index hiện có — phần còn lại thường là khớp biểu thức trong ORDER BY với index hoặc viết lại bằng join/CTE để tránh sort toàn bộ tập trung gian.