Window

ROW_NUMBER

PostgreSQLPostgreSQL

Memberikan nomor urut unik untuk setiap baris dalam partition, dimulai dari 1. Nomor tidak pernah duplikat dalam satu partition.

Tipe hasil: bigint

Syntax

SQL
ROW_NUMBER() OVER ([PARTITION BY partition_expression] ORDER BY sort_expression)

Parameter

ORDER BYclausewajib

Menentukan urutan penomoran dalam partition

PARTITION BYclauseopsional

Opsional. Membagi hasil ke dalam partition terpisah

Contoh Penggunaan

Penomoran Sederhana

SQL
1SELECT
2  ROW_NUMBER() OVER (ORDER BY created_at DESC) as row_num,
3  name,
4  created_at
5FROM products;

Memberikan nomor urut berdasarkan tanggal terbaru.

Hasil
row_numnamecreated_at
1iPhone 152024-03-15
2MacBook Pro2024-03-10
3AirPods2024-03-05

Top 3 per Kategori

SQL
1WITH ranked_products AS (
2  SELECT
3    category,
4    name,
5    price,
6    ROW_NUMBER() OVER (
7      PARTITION BY category
8      ORDER BY price DESC
9    ) as rank
10  FROM products
11)
12SELECT * FROM ranked_products WHERE rank <= 3;

Mengambil 3 produk termahal per kategori menggunakan ROW_NUMBER dengan PARTITION BY.

Hasil
categorynamepricerank
ElectronicsMacBook Pro25000001
ElectronicsiPhone 1515000002
ElectronicsAirPods3500003
ClothingJacket5000001
... 2 baris lainnya

Pagination dengan ROW_NUMBER

SQL
1SELECT *
2FROM (
3  SELECT
4    ROW_NUMBER() OVER (ORDER BY id) as row_num,
5    *
6  FROM products
7) numbered
8WHERE row_num BETWEEN 21 AND 40;  -- Page 2, 20 items per page

Implementasi pagination menggunakan ROW_NUMBER.

Kembali ke PostgreSQL Functions