Window

DENSE_RANK

PostgreSQL

Memberikan ranking tanpa gap. Berbeda dengan RANK yang membuat gap setelah nilai sama, DENSE_RANK melanjutkan dengan angka berikutnya. Berguna untuk top-N queries.

Tipe hasil: bigint

Syntax

SQL

DENSE_RANK() OVER (
  [PARTITION BY partition_expression]
  ORDER BY sort_expression [ASC|DESC]
)

Parameter

ORDER BYexpressionwajib

Menentukan urutan ranking

PARTITION BYexpressionopsional

Membagi data menjadi grup untuk ranking terpisah

Contoh Penggunaan

Perbedaan RANK vs DENSE_RANK

SQL

1 SELECT 
2   name,
3   score,
4   RANK() OVER (ORDER BY score DESC) AS rank,
5   DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
6 FROM students;

RANK memberi gap, DENSE_RANK tidak.

Hasil

name	score	rank	dense_rank
Andi	100	1	1
Budi	100	1	1
Citra	90	3	2
Dewi	85	4	3

Top 3 per Kategori

SQL

1 SELECT *
2 FROM (
3   SELECT 
4     category,
5     product_name,
6     sales,
7     DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
8   FROM products
9 ) ranked
10 WHERE rank <= 3;

Mengambil 3 produk terlaris per kategori.

Hasil

category	product_name	sales	rank
Electronics	Laptop	1000	1
Electronics	Phone	800	2
Electronics	Tablet	600	3

Ranking Nilai Unik

SQL

1 SELECT DISTINCT
2   salary,
3   DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_tier
4 FROM employees;

Membuat tier gaji tanpa gap.

Fungsi Equivalen di Database Lain

DENSE_RANK

MySQL

DENSE_RANK

Snowflake

DENSE_RANK

BigQuery

DENSE_RANK

DuckDB

Fungsi Terkait

ROW_NUMBERRANKPERCENT_RANKCUME_DISTNTILE

Kembali ke PostgreSQL Functions

1	SELECT
2	name,
3	score,
4	RANK() OVER (ORDER BY score DESC) AS rank,
5	DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
6	FROM students;

1	SELECT *
2	FROM (
3	SELECT
4	category,
5	product_name,
6	sales,
7	DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
8	FROM products
9	) ranked
10	WHERE rank <= 3;

1	SELECT DISTINCT
2	salary,
3	DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_tier
4	FROM employees;