Window

NTH_VALUE

DuckDB

Mengambil nilai ke-n dari window frame. Generalisasi dari FIRST_VALUE dan LAST_VALUE untuk posisi arbitrary.

Tipe hasil: Sama dengan tipe inputDiperbarui: 6 Jan 2026

Syntax

SQL

NTH_VALUE(expression, n) OVER (window_specification)

Parameter

expressionanywajib

Kolom atau ekspresi yang nilainya akan diambil

nINTEGERwajib

Posisi ke-n yang diinginkan (1-based index)

Contoh Penggunaan

Mengambil Nilai Kedua Tertinggi

SQL

1 SELECT
2   department,
3   employee_name,
4   salary,
5   NTH_VALUE(salary, 2) OVER (
6     PARTITION BY department
7     ORDER BY salary DESC
8     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
9   ) as second_highest_salary
10 FROM employees;

Mengambil gaji tertinggi kedua di setiap departemen.

Hasil

department	employee_name	salary	second_highest_salary
IT	Budi	25000000	22000000
IT	Ani	22000000	22000000
IT	Eko	18000000	22000000

Top 3 Products per Category

SQL

1 SELECT
2   category,
3   product_name,
4   sales,
5   NTH_VALUE(product_name, 1) OVER w as top1,
6   NTH_VALUE(product_name, 2) OVER w as top2,
7   NTH_VALUE(product_name, 3) OVER w as top3
8 FROM product_sales
9 WINDOW w AS (
10   PARTITION BY category
11   ORDER BY sales DESC
12   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
13 );

Mendapatkan top 3 produk per kategori.

Hasil

category	product_name	sales	top1	top2	top3
Electronics	Laptop	5000	Laptop	Phone	Tablet
Electronics	Phone	4500	Laptop	Phone	Tablet

Median-like Value

SQL

1 SELECT
2   department,
3   NTH_VALUE(salary, (COUNT(*) OVER w + 1) / 2) OVER w as median_salary
4 FROM employees
5 WINDOW w AS (
6   PARTITION BY department
7   ORDER BY salary
8   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
9 );

Mengambil nilai tengah (pseudo-median) dari salary.

Hasil

department	median_salary
IT	20000000
Sales	15000000
HR	12000000

Fungsi Terkait

LAGLEADFIRST_VALUELAST_VALUE

Kembali ke DuckDB Functions

1	SELECT
2	department,
3	employee_name,
4	salary,
5	NTH_VALUE(salary, 2) OVER (
6	PARTITION BY department
7	ORDER BY salary DESC
8	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
9	) as second_highest_salary
10	FROM employees;

1	SELECT
2	category,
3	product_name,
4	sales,
5	NTH_VALUE(product_name, 1) OVER w as top1,
6	NTH_VALUE(product_name, 2) OVER w as top2,
7	NTH_VALUE(product_name, 3) OVER w as top3
8	FROM product_sales
9	WINDOW w AS (
10	PARTITION BY category
11	ORDER BY sales DESC
12	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
13	);

1	SELECT
2	department,
3	NTH_VALUE(salary, (COUNT(*) OVER w + 1) / 2) OVER w as median_salary
4	FROM employees
5	WINDOW w AS (
6	PARTITION BY department
7	ORDER BY salary
8	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
9	);