Window

NTH_VALUE

MySQL

Mengembalikan nilai dari baris ke-n dalam window frame. Generalisasi dari FIRST_VALUE dan LAST_VALUE.

Tipe hasil: Same as exprDiperbarui: 7 Jan 2026

Syntax

SQL

NTH_VALUE(expr, n) OVER ([PARTITION BY col] ORDER BY col [frame_clause])

Parameter

exprexpressionwajib

Kolom atau ekspresi yang diambil nilainya

nintegerwajib

Posisi baris yang diinginkan (dimulai dari 1)

ORDER BYcolumn(s)wajib

Kolom untuk menentukan urutan

PARTITION BYcolumn(s)opsional

Kolom untuk membagi data menjadi grup (opsional)

Contoh Penggunaan

Nilai ke-2

SQL

1 SELECT name, score,
2   NTH_VALUE(score, 2) OVER (
3     ORDER BY score DESC
4     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
5   ) AS second_highest
6 FROM students;

Mendapatkan skor tertinggi kedua.

Hasil

(second highest score for comparison)

Median Sederhana

SQL

1 SELECT name, salary,
2   NTH_VALUE(salary, 3) OVER (
3     ORDER BY salary
4     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
5   ) AS third_salary
6 FROM top5_employees;

Mengambil nilai ke-3 dari 5 data (median).

Hasil

(third value as median)

Podium per Kategori

SQL

1 SELECT category, product, sales,
2   NTH_VALUE(product, 1) OVER w AS gold,
3   NTH_VALUE(product, 2) OVER w AS silver,
4   NTH_VALUE(product, 3) OVER w AS bronze
5 FROM products
6 WINDOW w AS (
7   PARTITION BY category 
8   ORDER BY sales DESC
9   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
10 );

Top 3 produk per kategori.

Hasil

(gold, silver, bronze per category)

Fungsi Terkait

LAGLEADFIRST_VALUELAST_VALUE

Kembali ke MySQL Functions

1	SELECT name, score,
2	NTH_VALUE(score, 2) OVER (
3	ORDER BY score DESC
4	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
5	) AS second_highest
6	FROM students;

1	SELECT name, salary,
2	NTH_VALUE(salary, 3) OVER (
3	ORDER BY salary
4	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
5	) AS third_salary
6	FROM top5_employees;

1	SELECT category, product, sales,
2	NTH_VALUE(product, 1) OVER w AS gold,
3	NTH_VALUE(product, 2) OVER w AS silver,
4	NTH_VALUE(product, 3) OVER w AS bronze
5	FROM products
6	WINDOW w AS (
7	PARTITION BY category
8	ORDER BY sales DESC
9	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
10	);