Window

ROW_NUMBER

Snowflake

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

Tipe hasil: INTEGERDiperbarui: 6 Jan 2026

Syntax

SQL

ROW_NUMBER() OVER ([PARTITION BY partition_expr] ORDER BY order_expr)

Parameter

ORDER BYclausewajib

Menentukan urutan penomoran dalam partition

PARTITION BYclauseopsional

Opsional. Membagi hasil ke dalam partition terpisah

Contoh Penggunaan

Penomoran Sederhana

SQL

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

Memberikan nomor urut berdasarkan tanggal terbaru.

Hasil

ROW_NUM	PRODUCT_NAME	CREATED_AT
1	iPhone 15	2024-03-15
2	MacBook Pro	2024-03-10
3	AirPods	2024-03-05

Top 3 per Kategori

SQL

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

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

Hasil

CATEGORY	PRODUCT_NAME	PRICE	RANK
Electronics	MacBook Pro	2500000	1
Electronics	iPhone 15	1500000	2
Electronics	AirPods	350000	3
Clothing	Jacket	500000	1
... 1 baris lainnya

Deduplication - Keep Latest

SQL

1 WITH deduplicated AS (
2   SELECT
3     *,
4     ROW_NUMBER() OVER (
5       PARTITION BY email
6       ORDER BY updated_at DESC
7     ) as rn
8   FROM users
9 )
10 SELECT * FROM deduplicated WHERE rn = 1;

Menghapus duplikat email, menyimpan record terbaru saja.

Fungsi Terkait

RANKDENSE_RANKPERCENT_RANKCUME_DISTNTILE

Kembali ke Snowflake Functions

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

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

1	WITH deduplicated AS (
2	SELECT
3	*,
4	ROW_NUMBER() OVER (
5	PARTITION BY email
6	ORDER BY updated_at DESC
7	) as rn
8	FROM users
9	)
10	SELECT * FROM deduplicated WHERE rn = 1;