NTH_VALUE function in SQL - Step-by-Step Execution

Concept Flow - NTH_VALUE function

Start with ordered data

↓

Define window partition and order

↓

Locate Nth row in window

↓

Return value from Nth row

↓

If Nth row missing, return NULL

↓

End

The NTH_VALUE function picks the value from the Nth row in a defined window of ordered data. If that row doesn't exist, it returns NULL.

Execution Sample

SQL

SELECT department, employee, salary,
       NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY salary DESC) AS second_highest_salary
FROM employees;

This query finds the second highest salary in each department and shows it alongside each employee.

Execution Table

Step	Row (department, employee, salary)	Window Partition	Order in Partition	NTH_VALUE(2) Result	Explanation
1	(Sales, Alice, 90000)	Sales	1 (highest salary)	85000	Second highest salary in Sales is 85000, returned for Alice
2	(Sales, Bob, 85000)	Sales	2	85000	Bob is second highest, so value is 85000
3	(Sales, Carol, 70000)	Sales	3	85000	Carol gets second highest salary 85000 as window is same
4	(IT, Dave, 120000)	IT	1	110000	Second highest salary in IT is 110000, returned for Dave
5	(IT, Eve, 110000)	IT	2	110000	Eve is second highest, value 110000
6	(IT, Frank, 95000)	IT	3	110000	Frank gets second highest salary 110000
7	(HR, Grace, 70000)	HR	1	NULL	Only one employee in HR, no second highest, returns NULL
8	End				No more rows to process

💡 All rows processed; NTH_VALUE returns NULL when Nth row does not exist in partition

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	After Row 7	Final
Current Row	None	(Sales, Alice, 90000)	(Sales, Bob, 85000)	(Sales, Carol, 70000)	(IT, Dave, 120000)	(IT, Eve, 110000)	(IT, Frank, 95000)	(HR, Grace, 70000)	None
Partition	None	Sales	Sales	Sales	IT	IT	IT	HR	None
Order Position	None	1	2	3	1	2	3	1	None
NTH_VALUE(2)	None	85000	85000	85000	110000	110000	110000	NULL	None

Key Moments - 3 Insights

Why does the NTH_VALUE function return the same value for all rows in a partition?

Because NTH_VALUE looks at the entire window partition and returns the value from the Nth row in the ordered set. This value is constant for all rows in that partition, as shown in execution_table rows 1-3 for Sales.

What happens if the partition has fewer rows than N in NTH_VALUE?

The function returns NULL because there is no Nth row to pick from. This is shown in execution_table row 7 for HR department.

Does NTH_VALUE depend on the current row's position in the partition?

No, it always returns the value from the fixed Nth row in the ordered partition, regardless of the current row's position, as seen in rows 4-6 for IT.

Visual Quiz - 3 Questions

Look at the execution_table, what is the NTH_VALUE(2) result for the row (Sales, Carol, 70000)?

A 70000

B 85000

C 90000

D NULL

💡 Hint

Check execution_table row 3 under 'NTH_VALUE(2) Result' column.

At which step does NTH_VALUE return NULL because the partition has fewer than 2 rows?

A Step 7

B Step 3

C Step 1

D Step 5

💡 Hint

Look at execution_table row 7 and the Explanation column.

If we change NTH_VALUE(salary, 3) in the query, what would be the NTH_VALUE result for (IT, Frank, 95000)?

A 120000

B 110000

C 95000

D NULL

💡 Hint

Refer to variable_tracker 'Order Position' and 'NTH_VALUE' values for IT partition rows.

Concept Snapshot

NTH_VALUE(expression, N) OVER (PARTITION BY ... ORDER BY ...)
- Returns the value of expression from the Nth row in the window.
- Window is defined by PARTITION BY and ORDER BY.
- If Nth row doesn't exist, returns NULL.
- Same value repeats for all rows in the partition.
- Useful to find Nth highest/lowest values in groups.

Full Transcript

The NTH_VALUE function in SQL returns the value from the Nth row of a window partition ordered by specified criteria. It first divides data into partitions, orders each partition, then picks the value from the Nth row in that order. If the partition has fewer than N rows, it returns NULL. The value returned is the same for every row in that partition. For example, to find the second highest salary per department, NTH_VALUE(salary, 2) is used with PARTITION BY department and ORDER BY salary DESC. This function helps to extract specific ranked values within groups in a table.