NTH_VALUE function in SQL

Introduction

The NTH_VALUE function helps you find the value at a specific position in a list of values, like picking the 3rd item from a sorted list.

When you want to find the 2nd highest salary in a company.

When you need to get the 5th earliest order date from a list of orders.

When you want to find the 1st, 2nd, or 3rd ranked student score in a class.

When you want to compare a value at a certain position within a group of data.

Syntax

SQL

NTH_VALUE(expression, n) OVER (PARTITION BY column ORDER BY column [ROWS BETWEEN ...])

The function returns the value of the expression at the nth row in the window.

You must use it with an OVER() clause that defines how to group and order the data.

Examples

Finds the 2nd highest salary among all employees.

SQL

SELECT employee_id, salary, NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest_salary FROM employees;

Finds the highest salary in each department.

SQL

SELECT department, employee_id, salary, NTH_VALUE(salary, 1) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_salary_in_dept FROM employees;

Finds the 3rd earliest order date overall.

SQL

SELECT order_id, order_date, NTH_VALUE(order_date, 3) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_order_date FROM orders;

Sample Program

This query finds the 2nd highest salary in each department and shows it alongside each employee's data.

SQL

CREATE TABLE employees (employee_id INT, department VARCHAR(20), salary INT);
INSERT INTO employees VALUES
(1, 'Sales', 5000),
(2, 'Sales', 7000),
(3, 'Sales', 6000),
(4, 'HR', 4500),
(5, 'HR', 4800);

SELECT employee_id, department, salary,
       NTH_VALUE(salary, 2) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest_salary_in_dept
FROM employees
ORDER BY department, salary DESC;

Important Notes

The window frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is often needed to get consistent results across all rows.

If n is larger than the number of rows in the window, the function returns NULL.

NTH_VALUE is different from ROW_NUMBER because it returns the value at a position, not the position number.

Summary

NTH_VALUE returns the value at the nth position in a sorted group of rows.

Use it with OVER() to define how to group and order your data.

It helps find specific ranked values like 2nd highest or 3rd earliest.