Bài 5: Sắp xếp và chọn một phần kết quả

Người phụ trách câu lạc bộ cần xem ba học viên có nhiều xu nhất. Nếu chỉ lấy ba người đầu tiên đang nhìn thấy, danh sách có thể bỏ mất người nhiều xu hơn. Hai bạn còn có số xu bằng nhau, nên báo cáo phải nói rõ cách xếp khi hòa. Bạn cần xác định thứ tự trước khi chọn số dòng.

Chọn chiều sắp xếp theo câu hỏi

Muốn xem ai ít xu trước, bạn cần xếp số xu từ nhỏ đến lớn. Mệnh đề ORDER BY yêu cầu thứ tự của các dòng kết quả. Chiều tăng dần (ascending) dùng từ khóa ASC, còn chiều giảm dần (descending) dùng DESC.

Bạn bắt đầu với mã học viên, một cột có giá trị khác nhau ở từng dòng:

SELECT id, name
FROM students
ORDER BY id ASC;

Kết quả đi từ mã 1 đến mã 6. Tên vẫn nằm cạnh đúng mã của học viên đó. SQL sắp cả dòng, không tách cột tên khỏi cột mã. Vì thế, thay đổi thứ tự không làm An trở thành người mang mã của Bình.

Nếu không viết chiều sau cột, ORDER BY id mặc định là tăng dần. Viết rõ ASC lúc mới học giúp người đọc nhìn ra ý định. Chiều giảm dần cần ghi DESC ngay sau cột muốn xếp giảm.

Bạn thử đảo chiều theo mã để so sánh với danh sách vừa đọc:

SELECT id, name
FROM students
ORDER BY id DESC;

Lan có mã 6 nên xuất hiện đầu tiên, An có mã 1 nên ở cuối. Đây là thứ tự theo mã, không nói ai nhiều xu hơn. Chọn đúng cột sắp xếp cũng cần thiết như chọn đúng chiều.

Khi không có ORDER BY, SQL không cam kết thứ tự dòng. Bạn có thể nhiều lần nhìn thấy cùng một thứ tự, nhưng đó chưa phải điều được câu lệnh yêu cầu. Với báo cáo cần xếp hạng, hãy viết quy tắc thay vì dựa vào cách bảng đang hiện.

Chọn vị trí cho thông tin thiếu

Khi xếp theo người hướng dẫn, một số ô mentor là NULL. Trong SQLite, chiều tăng dần đặt NULL trước các giá trị đã có. Bạn kiểm tra bằng cách xếp tên người hướng dẫn:

SELECT name, mentor
FROM students
ORDER BY mentor ASC;

Bình và Dung đứng đầu vì chưa có tên người hướng dẫn. Các tên Cô Mai và Thầy Nam đứng sau. Trong nhóm cùng người hướng dẫn, câu lệnh chưa quy định ai trước ai.

Chiều giảm dần đảo vị trí nhóm thiếu thông tin xuống cuối. Bạn đổi riêng chiều của mentor để đối chiếu:

SELECT name, mentor
FROM students
ORDER BY mentor DESC;

Nhóm Thầy Nam đứng trước nhóm Cô Mai, còn Bình và Dung ở cuối. Vị trí của NULL thay đổi theo chiều sắp xếp mặc định. Nếu muốn tên tăng dần nhưng ô thiếu vẫn ở cuối, bạn cần nêu riêng quy tắc đó.

Biểu thức mentor IS NULL bằng 0 với ô đã có tên và bằng 1 với ô thiếu. Xếp biểu thức này tăng dần sẽ đặt nhóm 0 trước nhóm 1. Sau đó, bạn xếp tên tăng trong từng nhóm:

SELECT name, mentor
FROM students
ORDER BY mentor IS NULL, mentor;

Các tên Cô Mai và Thầy Nam vẫn tăng dần, nhưng Bình và Dung đã xuống cuối. Cách viết này dùng phép kiểm tra IS NULL của bài 4 làm cột sắp xếp đầu tiên. Nó không điền tên vào ô thiếu hay thay dữ liệu nguồn.

Xác định thứ tự cả khi có người hòa

Bạn đổi sang xếp theo số xu để phục vụ báo cáo đầu bài. Trước hết, hãy nhìn bảng có mã, tên và số xu. Chi và Huy cùng 180 xu, nên chỉ nhìn số xu chưa phân biệt được ai đứng trước:

SELECT id, name, coins
FROM students
ORDER BY coins DESC;

Hai dòng 180 xu đứng trước Lan có 150 xu. Trong nhóm hòa, kết quả chạy lần này có một thứ tự cụ thể, nhưng câu lệnh chưa bảo đảm thứ tự đó. Bạn không nên suy ra máy sẽ tự ưu tiên mã nhỏ hoặc tên theo bảng chữ cái.

Cột phân xử hòa (tie-breaker) là cột tiếp theo dùng khi các giá trị sắp xếp trước đó bằng nhau. ORDER BY nhận nhiều cột, tách bằng dấu phẩy. Máy ưu tiên cột đầu, chỉ xét cột sau trong các nhóm đang hòa.

Bạn quy định người có mã nhỏ hơn đứng trước trong nhóm cùng số xu:

SELECT id, name, coins
FROM students
ORDER BY coins DESC, id ASC;

Chi đứng trước Huy vì 3 nhỏ hơn 5. Lan vẫn đứng sau cả hai vì mức ưu tiên đầu là số xu. Mã chỉ phân xử hòa, không biến người ít xu thành người đứng đầu.

Trong bảng này, mã học viên không trùng nên hai cột đủ xác định thứ tự của mọi dòng. Nếu cột phụ vẫn có thể trùng, bạn cần một quy tắc tiếp nữa. Việc dùng mã để phân xử là quy ước trình bày, không phải đánh giá ai học giỏi hơn.

Chiều của từng cột được viết riêng. ORDER BY coins, id DESC xếp xu tăng mặc định, rồi mã giảm khi hòa. Đặt DESC ở cuối danh sách không làm mọi cột phía trước cùng chuyển sang giảm.

Lấy một đoạn của danh sách đã sắp

Danh sách đầy đủ đã có thứ tự, nhưng người nhận chỉ cần ba dòng đầu. LIMIT đặt số dòng tối đa được trả về. Nó không phải điều kiện về mã và không yêu cầu số xu phải lớn hơn một ngưỡng.

Bạn kết hợp thứ tự đã xác định với giới hạn ba dòng:

SELECT id, name, coins
FROM students
ORDER BY coins DESC, id ASC
LIMIT 3;

Kết quả có Chi 180, Huy 180 và Lan 150. An có 120 xu không thuộc ba dòng đầu nên không được lấy. LIMIT 3 nghĩa là tối đa ba dòng; nếu chỉ có hai người thỏa bộ lọc, kết quả chỉ có hai.

Giới hạn không tự hiểu “nhiều nhất”. Nếu viết nhầm chiều tăng, câu lệnh vẫn chạy nhưng chọn sai người:

SELECT id, name, coins
FROM students
ORDER BY coins ASC, id ASC
LIMIT 3;

Bạn nhận Dung 60, Bình 90 và An 120. Máy đã thực hiện đúng chữ viết, nhưng chữ viết không đúng ý định. Cách sửa là chuyển chiều của coins sang DESC, còn LIMIT vẫn giữ nguyên.

Phân trang (pagination) chia một danh sách thành từng đoạn để xem lần lượt. OFFSET cho biết bỏ qua bao nhiêu dòng đầu của kết quả đã sắp. Sau đó, LIMIT quyết định lấy tối đa bao nhiêu dòng tiếp theo.

Bạn thử trang thứ hai có hai học viên mỗi trang, xếp theo mã tăng:

SELECT id, name
FROM students
ORDER BY id ASC
LIMIT 2 OFFSET 2;

Kết quả có Chi và Dung. Số 2 sau OFFSET là số dòng bị bỏ, không phải mã học viên cần tìm. Nếu mã bị khuyết, máy vẫn bỏ theo vị trí trong danh sách, không theo giá trị mã.

Nếu số dòng bỏ qua đã vượt độ dài kết quả, trang trả về không có dòng. Còn nếu phần cuối chỉ còn một dòng, trang chỉ có một dòng. Đó là các trạng thái bình thường khi lấy từng đoạn.

Loại trùng theo những cột thực sự được chọn

Người phụ trách thư mời lại cần danh sách các thành phố, mỗi thành phố chỉ xuất hiện một lần. Bạn không muốn xóa học viên trùng thành phố. Bạn chỉ cần bỏ các dòng kết quả giống nhau.

DISTINCT loại trùng trên toàn bộ các cột được chọn. Đặt nó ngay sau SELECT, trước danh sách cột. Với một cột thành phố, hãy xem các địa danh được trả về:

SELECT DISTINCT city
FROM students
ORDER BY city ASC;

Kết quả có bốn địa danh. Ba giá trị Hà Nội trong nguồn trở thành một dòng kết quả, còn các thành phố khác mỗi nơi có một dòng. Bảng students vẫn giữ đủ sáu học viên.

Nếu chọn thêm khối lớp, một dòng kết quả đại diện cho cặp thành phố và khối. Bạn có thể có nhiều dòng Hà Nội vì mỗi dòng đi kèm một khối khác. Hãy xem hai thuộc tính cùng được xét:

SELECT DISTINCT city, grade
FROM students
ORDER BY city ASC, grade ASC;

An, Chi và Lan đều ở Hà Nội nhưng có khối 6, 8 và 7. Ba cặp khác nhau nên đều có mặt. Trên bộ mẫu này, mọi cặp thành phố và khối đều khác, nên kết quả có sáu dòng.

Nếu thêm mã vào danh sách các thành phố, bạn cần kiểm lại việc loại trùng. Hãy đối chiếu số dòng với mục tiêu “mỗi thành phố một dòng”:

SELECT DISTINCT id, city
FROM students
ORDER BY id ASC;

Kết quả trở lại sáu dòng vì mỗi mã khác nhau. DISTINCT không chỉ xét cột cuối và cũng không tự bỏ qua mã. Muốn danh sách thành phố, hãy chọn đúng cột thể hiện đơn vị cần liệt kê.

Lỗi thường gặp

Tóm tắt

• ORDER BY sắp cả dòng. Mỗi cột có chiều ASC hoặc DESC riêng.
• Cột tiếp theo phân xử các dòng hòa ở cột trước. Mã không trùng giúp thứ tự được xác định.
• LIMIT lấy tối đa số dòng đã nêu, OFFSET bỏ số dòng đầu của danh sách đã sắp.
• DISTINCT xét trùng theo toàn bộ cột đầu ra và không xóa dữ liệu nguồn.

Kiểm tra nhanh

Đọc thêm

• SQLite: ORDER BY — tra thứ tự và cách phân xử khi hòa.
• SQLite: LIMIT và OFFSET — đọc quy tắc lấy một phần kết quả.
• SQLite: DISTINCT — xem cách loại dòng kết quả trùng nhau.