ORDER BY trong SQLite: sắp xếp ASC, DESC và theo nhiều cột

Không có ORDER BY, thứ tự các hàng là không xác định

Một câu SELECT không có ORDER BY trả về các hàng theo bất kỳ thứ tự nào mà SQLite thấy tiện. Trên một bảng nhỏ, thứ tự đó thường trông giống thứ tự lúc chèn dữ liệu, khiến nhiều người tin vào nó. Đừng tin. Ngay khi một chỉ mục (index) được dùng, bảng lớn lên, hoặc kế hoạch truy vấn thay đổi, thứ tự đó có thể đổi mà không báo trước.

Nếu bạn quan tâm đến thứ tự các hàng, hãy nói rõ ra:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country FROM users ORDER BY name;

ORDER BY name mặc định sắp xếp tăng dần. Kết quả trả về là Ada, Boris, Chen, Rosa, theo thứ tự bảng chữ cái, lần nào cũng vậy, bất kể bảng được lưu trên đĩa ra sao.

ASC và DESC

ASC nghĩa là tăng dần (từ nhỏ đến lớn, từ A đến Z, từ cũ nhất đến mới nhất). DESC nghĩa là giảm dần, tức ngược lại. ASC là mặc định, nên bạn gần như luôn thấy nó bị bỏ qua:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   '2024-01-05'),
    ('Rosa',  '2024-02-20'),
    ('Chen',  '2024-03-01');

SELECT name, signup_date FROM users ORDER BY signup_date DESC;

Câu này trả về những người đăng ký gần đây nhất trước. Ngày được lưu dưới dạng chuỗi ISO 8601 (YYYY-MM-DD) sắp xếp đúng khi so sánh như văn bản. Đó là một trong những lý do định dạng này được ưa chuộng cho các cột ngày trong SQLite, vốn không có kiểu ngày riêng.

Sắp xếp theo nhiều cột

Khi cột sắp xếp đầu tiên có các giá trị trùng nhau, SQLite dùng cột thứ hai để phân định. Liệt kê các cột cách nhau bằng dấu phẩy, theo thứ tự ưu tiên:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO users (name, country) VALUES
    ('Boris', 'FR'),
    ('Ada',   'US'),
    ('Rosa',  'FR'),
    ('Chen',  'US');

SELECT name, country FROM users ORDER BY country, name;

Các hàng được nhóm theo quốc gia trước (FR trước US), và trong mỗi quốc gia thì sắp xếp theo tên. Mỗi cột có thể mang chiều sắp xếp riêng:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country, signup_date
FROM users
ORDER BY country ASC, signup_date DESC;

Quốc gia tăng dần, rồi trong mỗi quốc gia thì mới nhất đứng trước. ASC và DESC chỉ áp dụng cho cột đứng ngay cạnh chúng, không lan sang cột khác.

Sắp xếp theo biểu thức và bí danh

ORDER BY nhận bất kỳ biểu thức nào, không chỉ tên cột. Điều này hữu ích với các giá trị được tính toán:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    quantity INTEGER
);

INSERT INTO products (name, price, quantity) VALUES
    ('Notebook', 4.50, 30),
    ('Pen',      1.20, 100),
    ('Lamp',    25.00, 5);

SELECT name, price * quantity AS revenue
FROM products
ORDER BY revenue DESC;

Bí danh (alias) revenue trong danh sách SELECT hoàn toàn dùng được trong ORDER BY. Bạn cũng có thể viết lại biểu thức, ORDER BY price * quantity DESC, và kết quả vẫn như vậy.

Bạn cũng có thể sắp xếp theo vị trí cột, dù đây là thói quen nên tránh:

SELECT name, price FROM products ORDER BY 2 DESC;

2 nghĩa là cột thứ hai trong danh sách select. Cách này chạy được, nhưng nếu sau này ai đó đổi thứ tự các cột, ý nghĩa của việc sắp xếp sẽ âm thầm thay đổi. Hãy sắp xếp theo tên cột hoặc bí danh.

NULL nằm ở đâu

NULL nghĩa là "không biết", và SQLite phải quyết định các giá trị không biết thuộc về đâu khi sắp xếp. Quy tắc mặc định: NULL đứng đầu với ASC, đứng cuối với DESC.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users ORDER BY signup_date;

Ada và Chen xuất hiện ở trên cùng, trước mọi ngày thật. Hiếm khi đó là điều bạn muốn khi cần "mới nhất trước". Thay đổi bằng NULLS LAST:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users
ORDER BY signup_date DESC NULLS LAST;

Giờ các ngày thật đứng trước, NULL nằm ở dưới cùng. NULLS FIRST làm điều ngược lại. Cả hai đều thuộc SQL chuẩn và chạy được trên SQLite 3.30 trở lên.

Sắp xếp không phân biệt hoa thường với COLLATE NOCASE

Mặc định SQLite so sánh văn bản theo kiểu nhị phân, tức sắp xếp theo mã Unicode. Điều đó có nghĩa là chữ hoa đứng trước chữ thường, nên 'Zoe' đứng trước 'apple':

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name;

Kết quả là Boris, Zoe, ada, apple: chữ hoa trước, rồi đến chữ thường. Để sắp xếp không phân biệt hoa thường, gắn collation NOCASE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name COLLATE NOCASE;

Giờ bạn nhận được ada, apple, Boris, Zoe. NOCASE chỉ coi các chữ ASCII từ A đến Z và từ a đến z là tương đương; nó không chuẩn hóa dấu hay các chữ cái ngoài ASCII. Để sắp xếp đúng cho nhiều ngôn ngữ, bạn cần một collation ở tầng ứng dụng, nhưng NOCASE vẫn ổn cho trường hợp tiếng Anh thông thường.

Thứ tự ngẫu nhiên

Đôi khi bạn muốn các hàng theo thứ tự ngẫu nhiên, ví dụ chọn một mục nổi bật mỗi ngày hoặc lấy mẫu vài hàng để kiểm thử. Hàm random() của SQLite trả về một số nguyên ngẫu nhiên; hãy sắp xếp theo nó:

CREATE TABLE quotes (id INTEGER PRIMARY KEY, text TEXT);

INSERT INTO quotes (text) VALUES
    ('Make it work, make it right, make it fast.'),
    ('Premature optimization is the root of all evil.'),
    ('Simple is better than complex.'),
    ('Readability counts.');

SELECT text FROM quotes ORDER BY random() LIMIT 1;

Mỗi hàng nhận một giá trị ngẫu nhiên mới, và phép sắp xếp xáo trộn chúng. Cách này ổn với bảng nhỏ. Trên bảng lớn, ORDER BY random() chạy chậm vì phải tính một giá trị ngẫu nhiên cho mọi hàng rồi sắp xếp toàn bộ kết quả. Để lấy mẫu một hàng từ một bảng khổng lồ, các cách tinh vi hơn (ví dụ chọn một rowid ngẫu nhiên) sẽ nhanh hơn.

Những lỗi thường gặp

Vài điều hay khiến người ta vấp:

• Quên ORDER BY mà vẫn mặc định có thứ tự. Không có nó, thứ tự là không xác định. Dù trông có vẻ ổn định, thật ra không phải.
• Sắp xếp số được lưu dưới dạng văn bản. Theo thứ tự từ điển, '10' đứng trước '2'. Nếu một cột cần sắp xếp theo số, hãy lưu nó với affinity kiểu số (hoặc ép kiểu: ORDER BY CAST(value AS INTEGER)).
• Trộn ASC và DESC giữa các cột. Mỗi cột có chiều riêng. ORDER BY a, b DESC sắp xếp a tăng dần và b giảm dần, chứ không phải cả hai đều giảm dần.
• Sắp xếp một tập kết quả khổng lồ chỉ để lấy vài hàng đầu. Kết hợp ORDER BY với LIMIT và đặt chỉ mục trên cột sắp xếp. Đó là nội dung của trang tiếp theo.

Tiếp theo: LIMIT và OFFSET

Sắp xếp cho SQLite biết cách xếp các hàng; LIMIT và OFFSET cho nó biết trả về bao nhiêu hàng và bắt đầu từ đâu. Kết hợp lại, chúng là nền tảng của phân trang và các truy vấn "top N", sẽ được giới thiệu ở trang tiếp theo.