WindowSQL standard

NTH_VALUE

Takes the value from the Nth row of the window: the second, the third…

Syntax

NTH_VALUE(<expression>, <n>) OVER (
  ORDER BY <sort key>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

NTH_VALUE(<expression>, <n>) OVER (
  PARTITION BY <group> ORDER BY <sort key>
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Like FIRST_VALUE, but for any number: NTH_VALUE(salary, 2) is the second salary in the window. The number of rows stays the same, and each gets the value from its own window. If the window doesn't have an Nth row yet, the result is NULL.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

The second salary next to each one

SELECT name, salary,

NTH_VALUE(salary, 2) OVER (

ORDER BY salary DESC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) AS second_salary

FROM employees;

name	salary	second_salary
Olivia	1350	1200
Emma	1200	1200
Sophia	1200	1200
Liam	1000	1200
Noah	950	1200

Without a frame — NULL at the start

SELECT name, salary,

NTH_VALUE(name, 2) OVER (ORDER BY salary DESC) AS second_so_far

FROM employees;

name	salary	second_so_far
Olivia	1350	NULL
Emma	1200	Emma
Sophia	1200	Emma
Liam	1000	Emma
Noah	950	Emma

Without a frame the window ends at the current row (together with its ties). For Olivia the window contains only her, so there's no second row yet.

The second name in each departmentAdvanced

SELECT name, department, salary,

NTH_VALUE(name, 2) OVER (

PARTITION BY department ORDER BY salary DESC

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) AS second_in_department

FROM employees;

name	department	salary	second_in_department
Emma	backend	1200	Liam
Liam	backend	1000	Liam
Sophia	design	1200	NULL
Olivia	frontend	1350	Noah
Noah	frontend	950	Noah

design has one person, so there's no second — NULL.

Watch out

Without the ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame, the first rows get NULL: the window ends at the current row, and there's no Nth row in it yet.

The second highest salary isn't always NTH_VALUE(salary, 2): if the top two are equal, the second row gives the same number as the first. The second distinct value is found by DENSE_RANK.

You can't use the result in the WHERE of the same query — calculate it in a subquery first and filter outside it.

Related functions

FIRST_VALUE DENSE_RANK LEAD