SELECT DISTINCT ON (expr) ... mengembalikan satu baris untuk setiap nilai unik expr: baris pertama dari kelompok itu menurut urutan ORDER BY. Urutkan dulu berdasarkan kunci kelompok, lalu berdasarkan apa yang menentukan "pertama". Untuk pesanan terbaru setiap pelanggan, bentuknya ORDER BY customer, created_at DESC.
Hasilnya tiga baris: pesanan 2 untuk asha, 4 untuk ben, dan 5 untuk chen, masing-masing yang paling baru. DISTINCT ON adalah extension PostgreSQL. Database lain butuh bentuk window function yang ditunjukkan di bawah.
Bagaimana ORDER BY menentukan baris yang disimpan
PostgreSQL mengurutkan baris, lalu menyimpan baris pertama setiap kali nilai DISTINCT ON berubah. Jadi:
ORDER BYharus diawali dengan ekspresiDISTINCT ON, atau PostgreSQL memunculkan "SELECT DISTINCT ON expressions must match initial ORDER BY expressions".- Tanpa
ORDER BYtambahan setelah ekspresi itu, baris mana yang disimpan tidak bisa diprediksi dan bisa berubah di setiap eksekusi.
Ganti created_at DESC dengan total DESC, dan query yang sama mengembalikan pesanan terbesar setiap pelanggan:
Sekarang baris asha adalah pesanan 1 (40) dan baris ben adalah pesanan 3 (60).
Atasi nilai seri dengan sengaja
Saat dua baris bernilai sama di kolom pengurutan, keduanya bisa menang. Tambahkan kunci pengurutan terakhir yang unik, seperti primary key, supaya hasilnya selalu sama:
Baris 2 dan 3 sama-sama punya 95 poin. id sebagai kunci terakhir membuat baris 2 menjadi pemenangnya.
Mengelompokkan berdasarkan lebih dari satu kolom
Tulis beberapa ekspresi untuk mendapatkan satu baris per kombinasi:
Setiap pasangan sensor dan jenis menyimpan pembacaan terbarunya: s1 humidity 40, s1 temp 22, s2 temp 19.
Versi portabel dengan ROW_NUMBER
ROW_NUMBER() memberi nomor pada baris di dalam setiap partisi. Menyimpan nomor 1 memberi hasil yang sama dengan DISTINCT ON, dan cara ini juga berjalan di MySQL 8, SQL Server, dan SQLite:
SELECT customer, id, total, created_at
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
FROM orders o
) ranked
WHERE rn = 1;
Pakai bentuk ini saat kamu butuh n teratas per kelompok (rn <= 3) atau saat query harus berjalan di database lain. Halaman window function menjelaskan PARTITION BY.
Membuatnya cepat
DISTINCT ON mengurutkan input, jadi di tabel besar beri index yang cocok dengan ORDER BY:
CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);
Dengan begitu PostgreSQL bisa membaca baris sesuai urutan alih-alih mengurutkannya. Dengan sedikit kelompok dan banyak baris per kelompok, join LATERAL yang mengambil LIMIT 1 per pelanggan dari index yang sama biasanya membaca lebih sedikit baris. Halaman join menunjukkan LATERAL. Bandingkan keduanya dengan EXPLAIN ANALYZE di datamu.
Pertanyaan yang sering diajukan
Apa perbedaan DISTINCT dan DISTINCT ON?
DISTINCT menghapus baris yang identik di setiap kolom yang dipilih. DISTINCT ON (a) menyimpan satu baris untuk setiap nilai a, dan kolom lainnya diambil dari baris mana pun yang berada paling atas dalam urutan kelompok itu.
Apakah DISTINCT ON termasuk SQL standar?
Bukan. Ini extension PostgreSQL, jadi tidak berjalan di MySQL atau SQL Server. Versi yang portabel adalah ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) di dalam subquery, difilter ke nomor baris 1.
Kenapa muncul "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?
ORDER BY harus diawali dengan ekspresi yang sama dengan DISTINCT ON, dalam urutan yang sama. Tulis DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, bukan hanya ORDER BY created_at DESC.
Bisakah DISTINCT ON mengembalikan 3 baris teratas per kelompok?
Tidak, ia mengembalikan tepat satu baris per kelompok. Untuk n teratas, beri nomor baris dengan ROW_NUMBER() dan simpan rn <= 3, atau pakai subquery LATERAL dengan LIMIT 3.