ORDER BY

Topik ini membahas klausa ORDER BY yang menetapkan urutan baris pada hasil query, mulai dari satu kolom, beberapa kolom dengan arah campuran, kolom yang tidak ditampilkan, hingga posisi NULL dan pengaruh collation (aturan pembandingan teks). Tabel latihannya tetap tabel buku perpustakaan yang dipakai di seluruh modul ini. Setelah membaca, hasil query dapat disusun dengan urutan yang terjamin dan perilaku pengurutan teks serta NULL dapat diprediksi.

Dataset contoh#

Blok berikut membangun ulang tabel buku dan menampilkannya tanpa klausa pengurutan.

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, pengarang TEXT, tahun INTEGER, stok INTEGER);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Ali', 2019, 4),
  (2, 'Peta Langit', 'Budi', 2021, 0),
  (3, 'Dasar Algoritma', 'Citra', 2021, 2),
  (4, 'Sejarah Kertas', 'Dewi', 2019, 3),
  (5, 'Kode dan Sandi', 'Eka', 2023, 5),
  (6, 'Jejak Angin', 'Farhan', 2020, 0),
  (7, 'Peta Langit', 'Budi', 2024, 7);
SELECT * FROM buku;

1|Dasar Algoritma|Ali|2019|4
2|Peta Langit|Budi|2021|0
3|Dasar Algoritma|Citra|2021|2
4|Sejarah Kertas|Dewi|2019|3
5|Kode dan Sandi|Eka|2023|5
6|Jejak Angin|Farhan|2020|0
7|Peta Langit|Budi|2024|7

Keluaran ini tampil sesuai urutan penyisipan karena tabelnya kecil, tetapi urutan semacam itu tidak dijamin oleh SQL. Mesin basis data bebas mengembalikan baris dalam urutan apa pun bila query tidak menyertakan ORDER BY. Konsekuensinya, urutan yang dibutuhkan pembaca selalu dinyatakan secara eksplisit dengan klausa ini. Klausa tersebut tidak mengubah isi tabel, karena urutan hanya berlaku pada keluaran query yang sedang berjalan. Penyimpanan fisik baris pun tidak dipindahkan, sehingga pengurutan perlu dituliskan kembali pada setiap query baru.

Blok pada bagian-bagian berikutnya membangun ulang tabel yang sama, sehingga setiap pengurutan dapat diverifikasi ulang sendiri. Kolom id yang berurutan 1 hingga 7 memudahkan penelusuran, karena setiap baris pada keluaran dapat dilacak kembali ke baris penyisipannya. Dua judul kembar dan dua pasang tahun kembar pada tabel ini menjadi bahan uji yang baik, karena perilaku pengurutan paling terlihat justru ketika ada nilai yang seri.

Pengurutan satu kolom#

ORDER BY diikuti nama kolom akan menyusun baris dari nilai terkecil ke terbesar, karena arah ASC adalah nilai default yang tidak perlu ditulis. Jalankan query berikut untuk mengurutkan buku menurut tahun terbit.

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, pengarang TEXT, tahun INTEGER, stok INTEGER);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Ali', 2019, 4),
  (2, 'Peta Langit', 'Budi', 2021, 0),
  (3, 'Dasar Algoritma', 'Citra', 2021, 2),
  (4, 'Sejarah Kertas', 'Dewi', 2019, 3),
  (5, 'Kode dan Sandi', 'Eka', 2023, 5),
  (6, 'Jejak Angin', 'Farhan', 2020, 0),
  (7, 'Peta Langit', 'Budi', 2024, 7);
SELECT judul, tahun FROM buku ORDER BY tahun;

Dasar Algoritma|2019
Sejarah Kertas|2019
Jejak Angin|2020
Peta Langit|2021
Dasar Algoritma|2021
Kode dan Sandi|2023
Peta Langit|2024

Tahun 2019 dan 2021 masing-masing dimiliki dua buku, sehingga muncul pasangan baris yang nilainya seri. Urutan antarbaris yang seri tidak dijamin oleh SQL, sehingga bila urutan di dalam seri itu penting, kolom pemecah seri ditambahkan setelahnya. Arah sebaliknya ditulis dengan kata DESC.

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, pengarang TEXT, tahun INTEGER, stok INTEGER);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Ali', 2019, 4),
  (2, 'Peta Langit', 'Budi', 2021, 0),
  (3, 'Dasar Algoritma', 'Citra', 2021, 2),
  (4, 'Sejarah Kertas', 'Dewi', 2019, 3),
  (5, 'Kode dan Sandi', 'Eka', 2023, 5),
  (6, 'Jejak Angin', 'Farhan', 2020, 0),
  (7, 'Peta Langit', 'Budi', 2024, 7);
SELECT judul, tahun FROM buku ORDER BY tahun DESC;

Peta Langit|2024
Kode dan Sandi|2023
Peta Langit|2021
Dasar Algoritma|2021
Jejak Angin|2020
Dasar Algoritma|2019
Sejarah Kertas|2019

Keluaran ini merupakan kebalikan persis dari yang sebelumnya, dari terbitan termuda hingga tertua. Query daftar terbaru atau daftar teratas hampir selalu memakai bentuk ini, biasanya disertai LIMIT agar jumlah barisnya terkendali. Bentuk ORDER BY tahun ASC berbunyi sama persis dengan bentuk tanpa kata ASC pada query pertama. Pengurutan juga berlaku untuk kolom teks dan tanggal, yang dibandingkan menurut aturan pembandingannya masing-masing. Untuk kolom teks biasa, pembandingannya peka huruf besar-kecil, dan pengaturannya dibahas pada bagian collation di bawah, sedangkan kolom angka memakai urutan bilangannya sehingga 2 tetap mendahului 10.

Pengurutan multi kolom#

Beberapa kolom dituliskan dengan dipisah koma, dan SQLite menilainya dari kiri ke kanan. Kolom pertama membentuk kelompok besar, sedangkan kolom kedua mengurutkan di dalam setiap kelompok yang nilainya seri. Arah ditetapkan per kolom, sehingga arah campuran seperti berikut menjadi mungkin.

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, pengarang TEXT, tahun INTEGER, stok INTEGER);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Ali', 2019, 4),
  (2, 'Peta Langit', 'Budi', 2021, 0),
  (3, 'Dasar Algoritma', 'Citra', 2021, 2),
  (4, 'Sejarah Kertas', 'Dewi', 2019, 3),
  (5, 'Kode dan Sandi', 'Eka', 2023, 5),
  (6, 'Jejak Angin', 'Farhan', 2020, 0),
  (7, 'Peta Langit', 'Budi', 2024, 7);
SELECT judul, pengarang, tahun FROM buku
  ORDER BY tahun DESC, judul;

Peta Langit|Budi|2024
Kode dan Sandi|Eka|2023
Dasar Algoritma|Citra|2021
Peta Langit|Budi|2021
Jejak Angin|Farhan|2020
Dasar Algoritma|Ali|2019
Sejarah Kertas|Dewi|2019

Tahun tetap menurun dari 2024 ke 2019, sedangkan di dalam tahun 2021 dan 2019 judul tersusun menaik. Dua baris Dasar Algoritma berjauhan karena keduanya tergabung ke kelompok tahunnya masing-masing. Pola arah campuran seperti ini lazim untuk laporan, misalnya kelompok terbaru lebih dahulu tetapi isinya tersusun menurut nama.

Jumlah kolom kunci tidak dibatasi dua, dan SQLite menilainya berurutan hingga barisnya benar-benar terbedakan. Bila seluruh kunci berakhir seri, urutan akhirnya tidak dijamin, dan menambahkan kolom id sebagai kunci terakhir adalah cara yang lazim untuk memaksa urutan yang menentu. Aturan penilaian kiri ke kanan inilah yang menjadikan urutan penulisan kolom sama pentingnya dengan arahnya. Kata ASC boleh ditulis eksplisit pada kolom kedua, sehingga ORDER BY tahun DESC, judul ASC berbunyi sama dengan contoh di atas.

Kolom sortir yang tidak ditampilkan#

Kolom pada ORDER BY tidak harus masuk daftar SELECT. Query berikut menampilkan judul saja, tetapi mengurutkannya menurut stok yang tidak tampil.

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, pengarang TEXT, tahun INTEGER, stok INTEGER);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Ali', 2019, 4),
  (2, 'Peta Langit', 'Budi', 2021, 0),
  (3, 'Dasar Algoritma', 'Citra', 2021, 2),
  (4, 'Sejarah Kertas', 'Dewi', 2019, 3),
  (5, 'Kode dan Sandi', 'Eka', 2023, 5),
  (6, 'Jejak Angin', 'Farhan', 2020, 0),
  (7, 'Peta Langit', 'Budi', 2024, 7);
SELECT judul FROM buku ORDER BY stok DESC, id;

Peta Langit
Kode dan Sandi
Dasar Algoritma
Sejarah Kertas
Dasar Algoritma
Peta Langit
Jejak Angin

Baris tersusun dari stok terbesar 7 hingga terkecil 0, dan id disertakan sebagai pemecah seri untuk dua buku yang sama-sama berstok 0, yaitu Peta Langit dan Jejak Angin. Kemampuan ini berguna ketika urutan logisnya terletak pada kolom yang tidak relevan untuk ditampilkan, misalnya mengurutkan daftar buku terpopuler menurut jumlah peminjamannya yang tidak ikut ditampilkan. SQLite membiarkan kolom sortir lepas dari daftar hasil, berbeda dari beberapa mesin basis data yang mensyaratkannya berada pada proyeksi. Sifat ini berlaku pula sebaliknya, yaitu kolom yang ditampilkan tidak harus menjadi kunci pengurutan, sehingga kedua daftar itu saling bebas dalam batas keterbacaannya. Meskipun begitu, bila hasilnya hendak disajikan kepada pengguna, pastikan proyeksinya memuat kolom yang membuat setiap baris dikenali, karena urutan tanpa penanda membingungkan pembaca laporan. Kolom sortir bahkan boleh berupa ekspresi, sebagaimana kolom pada daftar SELECT.

Posisi NULL dalam urutan#

Dataset pada bagian ini diperluas satu baris berjudul Suara Hujan yang stoknya belum dicatat, sehingga kolom stoknya bernilai NULL. Blok berikut membangun tabel yang diperluas itu lalu mengurutkannya menurut stok.

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, pengarang TEXT, tahun INTEGER, stok INTEGER);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Ali', 2019, 4),
  (2, 'Peta Langit', 'Budi', 2021, 0),
  (3, 'Dasar Algoritma', 'Citra', 2021, 2),
  (4, 'Sejarah Kertas', 'Dewi', 2019, 3),
  (5, 'Kode dan Sandi', 'Eka', 2023, 5),
  (6, 'Jejak Angin', 'Farhan', 2020, 0),
  (7, 'Peta Langit', 'Budi', 2024, 7),
  (8, 'Suara Hujan', 'Dewi', 2018, NULL);
SELECT judul, stok FROM buku ORDER BY stok;

Suara Hujan|
Peta Langit|0
Jejak Angin|0
Dasar Algoritma|2
Sejarah Kertas|3
Dasar Algoritma|4
Kode dan Sandi|5
Peta Langit|7

Suara Hujan tampil paling atas, dan posisi stoknya pada keluaran berupa kolom kosong karena NULL digambar tanpa karakter oleh sqlite3. SQLite memperlakukan NULL sebagai nilai yang lebih kecil daripada nilai apa pun, sehingga baris NULL mendahului baris lain pada urutan menaik. Arah menurun otomatis membalik posisinya.

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, pengarang TEXT, tahun INTEGER, stok INTEGER);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Ali', 2019, 4),
  (2, 'Peta Langit', 'Budi', 2021, 0),
  (3, 'Dasar Algoritma', 'Citra', 2021, 2),
  (4, 'Sejarah Kertas', 'Dewi', 2019, 3),
  (5, 'Kode dan Sandi', 'Eka', 2023, 5),
  (6, 'Jejak Angin', 'Farhan', 2020, 0),
  (7, 'Peta Langit', 'Budi', 2024, 7),
  (8, 'Suara Hujan', 'Dewi', 2018, NULL);
SELECT judul, stok FROM buku ORDER BY stok DESC;

Peta Langit|7
Kode dan Sandi|5
Dasar Algoritma|4
Sejarah Kertas|3
Dasar Algoritma|2
Peta Langit|0
Jejak Angin|0
Suara Hujan|

Suara Hujan kini menempati baris terakhir. Apabila posisi NULL perlu dikunci tanpa bergantung pada arah, SQLite menyediakan tulisan NULLS FIRST dan NULLS LAST yang dapat ditambahkan setelah nama kolom. Posisi yang stabil seperti ini penting pada laporan, karena baris yang belum lengkap tidak boleh bergeser liar antareksekusi.

Collation BINARY dan NOCASE#

Urutan dua nilai teks ditentukan oleh collation, yaitu aturan pembandingan karakter yang dipakai saat pengurutan dan perbandingan. Collation bawaan bernama BINARY membandingkan kode karakter satu per satu, sehingga seluruh huruf besar ASCII dengan kodenya 65 hingga 90 mendahului huruf kecil berkode 97 hingga 122. Blok berikut membangun tabel katalog kecil yang penulisannya tidak konsisten, lalu mengurutkannya dengan BINARY.

CREATE TABLE katalog (judul TEXT);
INSERT INTO katalog VALUES
  ('angin'),
  ('Bulan'),
  ('cermin'),
  ('DARAT'),
  ('Emas');
SELECT judul FROM katalog ORDER BY judul COLLATE BINARY;

Bulan
DARAT
Emas
angin
cermin

Huruf besar seluruhnya mendahului huruf kecil, sehingga urutannya terlihat janggal bagi pembaca manusia. Collation NOCASE melipat huruf ASCII a-z sebelum membandingkan, dan hasilnya mendekati urutan kamus.

CREATE TABLE katalog (judul TEXT);
INSERT INTO katalog VALUES
  ('angin'),
  ('Bulan'),
  ('cermin'),
  ('DARAT'),
  ('Emas');
SELECT judul FROM katalog ORDER BY judul COLLATE NOCASE;

angin
Bulan
cermin
DARAT
Emas

Kelima judul kini tersusun tanpa memandang besar kecilnya huruf. Lipatan NOCASE hanya mencakup huruf ASCII, sehingga karakter beraksen maupun aksara non-Latin tetap dibandingkan berdasarkan kodenya. Bila aturan NOCASE diinginkan untuk seluruh query pada kolom itu, pendekatan yang lebih ringkas adalah menyatakannya sekali pada definisi kolom, misalnya judul TEXT COLLATE NOCASE. Dengan begitu setiap pengurutan dan perbandingan pada kolom tersebut memakai aturan yang sama tanpa perlu kata COLLATE berulang kali.

Kesalahan umum#

Kesalahan yang sering terjadi adalah mengira kata DESC berlaku untuk seluruh daftar kolom, padahal arah hanya melekat pada kolom yang berada tepat sebelumnya. Query berikut bermaksud mengurutkan tahun dan judul sama-sama menurun.

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, pengarang TEXT, tahun INTEGER, stok INTEGER);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', 'Ali', 2019, 4),
  (2, 'Peta Langit', 'Budi', 2021, 0),
  (3, 'Dasar Algoritma', 'Citra', 2021, 2),
  (4, 'Sejarah Kertas', 'Dewi', 2019, 3),
  (5, 'Kode dan Sandi', 'Eka', 2023, 5),
  (6, 'Jejak Angin', 'Farhan', 2020, 0),
  (7, 'Peta Langit', 'Budi', 2024, 7);
SELECT judul, tahun FROM buku ORDER BY tahun, judul DESC;

Sejarah Kertas|2019
Dasar Algoritma|2019
Jejak Angin|2020
Peta Langit|2021
Dasar Algoritma|2021
Kode dan Sandi|2023
Peta Langit|2024

Tahun justru tersusun menaik dari 2019 ke 2024, sementara hanya judul yang menurun di dalam tiap kelompoknya. Perbaikannya adalah menuliskan arah pada setiap kolom, yaitu ORDER BY tahun DESC, judul DESC. Perbedaan hasil ini dengan maksud semula menunjukkan betapa menentukannya posisi kata DESC pada daftar kolom.

Kesalahan lain yang lazim adalah mengurutkan kolom teks yang isinya angka, karena urutan teksnya berbeda dari urutan bilangannya. Sebagai teks, angka 10 mendahului angka 2 karena karakter 1 berkode lebih kecil daripada 2, dan hasilnya tampak kacau bila dibaca sebagai bilangan. Perbaikannya adalah menyimpan nilai semacam itu sebagai INTEGER, atau mengonversinya saat query dengan CAST bila penyimpanannya sudah tidak dapat diubah. Kasus serupa juga menyerang kolom tanggal yang tersimpan sebagai teks, dan format ISO pada tabel peminjaman aman karena urutan teksnya sejalan dengan urutan waktunya.

Ringkasan#

• ORDER BY menilai kolom dari kiri ke kanan, dan arah ASC atau DESC hanya melekat pada kolom yang tepat berada sebelumnya.
• Kolom sortir tidak harus berada pada daftar SELECT dan boleh berupa ekspresi.
• SQLite menganggap NULL lebih kecil daripada nilai lain, sehingga NULL tampil lebih dahulu pada urutan menaik dan NULLS FIRST atau NULLS LAST dapat mengunci posisinya.
• Collation BINARY membandingkan kode karakter, sedangkan NOCASE melipat huruf ASCII a-z sebelum membandingkan.

Referensi#

• SELECT — Dokumentasi SQLite — Aturan ORDER BY, arah pengurutan, dan perlakuan NULL.
• Ekspresi — Dokumentasi SQLite — Operator COLLATE dan daftar collation bawaan SQLite.