ORDER BY dan LIMIT

Idenya

Tanyakan "apa lima produk termahal kita?" dan kamu butuh dua hal: urutkan produk berdasarkan harga, tertinggi dulu, dan simpan hanya lima yang pertama. Itulah ORDER BY dan LIMIT.

Ini sesuatu yang mengejutkan sebagian besar pemula: tanpa ORDER BY, database sama sekali tidak menjanjikan urutan. Baris-baris sering kembali sesuai urutan dimasukkannya, tapi itu kebetulan, bukan aturan. Setelah sebuah update atau di tabel yang lebih besar, urutannya bisa berubah. Kalau urutan itu penting, nyatakan.

Mengurutkan

Tabel di database tidak punya urutan yang dijamin. Baris-barisnya dikembalikan dalam urutan apa pun yang praktis bagi database, yang bisa berubah dari hari ke hari. Kalau urutan penting untuk pertanyaanmu, dan biasanya memang penting, kamu harus memintanya dengan ORDER BY beserta kolom yang dipakai untuk mengurutkan. Ini produk-produk dari yang termurah sampai yang termahal:

SELECT name, price
FROM products
ORDER BY price;

 name                 | price
----------------------+--------
 Gel Pen Set          |  38000
 Notebook A5 Dotted   |  45000
 Ceramic Mug          |  55000
 Bamboo Plant         |  60000
 Robusta Lampung 250g |  65000
...
(15 rows)

Arah default-nya adalah ascending (ASC, menaik): terkecil dulu, A sebelum Z, tanggal terlama dulu. Tambahkan DESC untuk descending (menurun):

SELECT name, price
FROM products
ORDER BY price DESC;

 name                 | price
----------------------+--------
 Pour-over Kettle     | 320000
 USB-C Hub            | 289000
 Desk Lamp LED        | 245000
 French Press 600ml   | 185000
 Wireless Mouse       | 175000
...
(15 rows)

Mengurutkan berdasarkan beberapa kolom

Daftarkan beberapa kolom, dipisahkan koma. Kolom kedua hanya menentukan urutan saat kolom pertama seri:

SELECT name, category, price
FROM products
ORDER BY category, price DESC;

 name                 | category    | price
----------------------+-------------+--------
 Toraja Kalosi 250g   | Coffee      | 110000
 Arabica Gayo 250g    | Coffee      |  95000
 Robusta Lampung 250g | Coffee      |  65000
 USB-C Hub            | Electronics | 289000
 Desk Lamp LED        | Electronics | 245000
 Wireless Mouse       | Electronics | 175000
 Throw Pillow         | Home        | 120000
 Scented Candle       | Home        |  75000
 Bamboo Plant         | Home        |  60000
 Pour-over Kettle     | Kitchen     | 320000
 French Press 600ml   | Kitchen     | 185000
 Ceramic Mug          | Kitchen     |  55000
 Desk Organizer       | Stationery  |  89000
 Notebook A5 Dotted   | Stationery  |  45000
 Gel Pen Set          | Stationery  |  38000
(15 rows)

Bacalah sebagai: "kelompokkan berdasarkan kategori secara alfabetis, dan di dalam setiap kategori, termahal dulu". Setiap kolom punya arahnya sendiri: category menaik (default) dan price menurun.

Kamu juga bisa mengurutkan berdasarkan kolom yang tidak kamu pilih: SELECT name FROM products ORDER BY price; bekerja dengan baik.

LIMIT: N teratas

Kembali ke pertanyaan di awal: tiga produk termahal. Mengurutkan berdasarkan harga, dari yang tertinggi, menaruhnya di paling atas, tapi kamu tetap mendapat ke-15 baris. LIMIT n hanya menyimpan n baris pertama, dan diterapkan setelah diurutkan, jadi kamu mendapat tiga teratas:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

 name             | price
------------------+--------
 Pour-over Kettle | 320000
 USB-C Hub        | 289000
 Desk Lamp LED    | 245000
(3 rows)

Jebakan nilai seri

Pelanggan mana yang memesan paling baru? Beberapa pesanan punya tanggal yang sama:

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 4;

 order_id | order_date
----------+------------
       40 | 2024-06-29
       39 | 2024-06-25
       38 | 2024-06-25
       37 | 2024-06-24
(4 rows)

Pesanan 38 dan 39 sama-sama dibuat pada 25 Juni. Dengan LIMIT 2, mana yang masuk? Database bebas memilih salah satunya, dan bisa memilih yang berbeda lain kali. Laporan yang berubah padahal tidak ada yang mengubah datanya adalah mimpi buruk untuk di-debug.

Perbaikannya adalah tie-breaker (pemecah seri): tambahkan kolom yang unik, biasanya primary key:

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 2;

 order_id | order_date
----------+------------
       40 | 2024-06-29
       39 | 2024-06-25
(2 rows)

Sekarang urutannya sepenuhnya ditentukan: di antara pesanan pada tanggal yang sama, order_id yang lebih tinggi menang, setiap kali.

Di mana NULL ditaruh

stock bernilai NULL untuk Bamboo Plant. Di PostgreSQL, NULL diurutkan seolah lebih besar dari setiap nilai: terakhir dalam urutan menaik, pertama dalam urutan menurun. Sering kali itu bukan yang kamu inginkan:

SELECT name, stock
FROM products
ORDER BY stock DESC
LIMIT 3;

 name               | stock
--------------------+-------
 Bamboo Plant       |  NULL
 Notebook A5 Dotted |   200
 Ceramic Mug        |   150
(3 rows)

Tanaman dengan stok yang tidak diketahui bukanlah produk dengan stok terbanyak. Nyatakan di mana NULL seharusnya ditaruh dengan NULLS LAST (atau NULLS FIRST):

SELECT name, stock
FROM products
ORDER BY stock DESC NULLS LAST
LIMIT 3;

 name                 | stock
----------------------+-------
 Notebook A5 Dotted   |   200
 Ceramic Mug          |   150
 Robusta Lampung 250g |   120
(3 rows)

OFFSET: menelusuri hasil per halaman

Situs web menampilkan hasil per halaman: baris 1 sampai 10, lalu 11 sampai 20. OFFSET n melewati n baris pertama:

SELECT order_id, order_date
FROM orders
ORDER BY order_date, order_id
LIMIT 5 OFFSET 5;

 order_id | order_date
----------+------------
        6 | 2024-01-20
        7 | 2024-01-20
        8 | 2024-02-03
        9 | 2024-02-14
       10 | 2024-02-14
(5 rows)

Itu halaman 2 dengan 5 baris per halaman. Halaman p adalah LIMIT 5 OFFSET (p - 1) * 5. Paging hanya masuk akal dengan urutan yang sepenuhnya ditentukan (perhatikan tie-breaker order_id), atau baris bisa muncul di dua halaman atau tidak di mana pun.

Urutan klausa

Kamu menulis klausa-klausa dalam urutan ini, dan database menjalankannya dalam urutan ini:

Filter dulu, lalu pilih kolom, lalu urutkan, lalu potong. Menulisnya dalam urutan yang berbeda (misalnya ORDER BY sebelum WHERE) adalah syntax error.

Mengapa ini penting untuk kode bersih

"Di komputerku jalan, di production angkanya beda" sering kali masalah urutan. Bersikap eksplisit soal urutan, dengan tie-breaker pada kolom unik dan NULLS LAST di tempat yang perlu, membuat query-mu deterministik: data yang sama selalu memberi hasil yang sama. Itulah fondasi laporan yang bisa dipercaya orang.

Rangkuman

• Tanpa ORDER BY berarti tidak ada urutan yang dijamin.
• ASC adalah default; DESC membaliknya. Setiap kolom pengurutan punya arahnya sendiri.
• Kolom pengurutan berikutnya hanya memecah seri di kolom sebelumnya.
• LIMIT n menyimpan n baris pertama setelah diurutkan; OFFSET n melewati baris untuk paging.
• Tambahkan kolom tie-breaker yang unik supaya hasilnya deterministik.
• NULL diurutkan terakhir di ASC dan pertama di DESC; atur dengan NULLS LAST / NULLS FIRST.