NTH_VALUE

Returns the value of an expression at the Nth row within the current window partition.

Limitations

Window functions are supported only in SELECT statements.
A window function cannot contain nested window functions or nested aggregate functions.
Window functions cannot be combined with aggregate functions at the same query level.

Syntax

nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

Parameters

Parameter	Required	Type	Description
`expr`	Yes	—	The expression whose value is returned.
`number`	Yes	BIGINT	The row position within the window. Must be an integer >= 1. When set to `1`, equivalent to FIRST_VALUE.
`ignore_nulls`	No	BOOLEAN	Whether to ignore null values. Default: `false`. When `true`, returns a non-null value of `expr` that corresponds to the Nth row.
`partition_clause`, `orderby_clause`, `frame_clause`	—	—	Standard window specification clauses. See windowing_definition.

Return value

A value of the same data type as expr. Returns NULL if the window contains fewer rows than number.

Examples

Examples using the emp table

The following examples use a shared emp table. Run the setup statements once before executing the queries.

Set up sample data

CREATE TABLE IF NOT EXISTS emp (
  empno   BIGINT,
  ename   STRING,
  job     STRING,
  mgr     BIGINT,
  hiredate DATETIME,
  sal     BIGINT,
  comm    BIGINT,
  deptno  BIGINT
);

tunnel upload emp.txt emp;

The emp.txt file contains 17 employee records across departments 10, 20, and 30:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Example 1: NTH_VALUE without ORDER BY

Retrieve the salary of the 6th employee in each department. Without ORDER BY, the window covers all rows in the partition, so every row returns the same value.

SELECT deptno, ename, sal,
       nth_value(sal, 6) OVER (PARTITION BY deptno) AS nth_value
FROM emp;

Result:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | JACCKA     | 5000       | 2450       |
| 10         | WELAN      | 2450       | 2450       |   -- 6th row; its sal is 2450.
| 20         | FORD       | 3000       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |   -- Fewer than 6 rows in this partition; NULL is returned.
| 30         | TURNER     | 1500       | 2850       |
| 30         | JAMES      | 950        | 2850       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | WARD       | 1250       | 2850       |
| 30         | MARTIN     | 1250       | 2850       |
| 30         | BLAKE      | 2850       | 2850       |   -- 6th row; its sal is 2850.
+------------+------------+------------+------------+

Department 20 has only 5 employees, so position 6 never exists and NULL is returned for all rows in that partition.

Example 2: NTH_VALUE with ORDER BY

With ORDER BY, the window grows from the first row to the current row. A row returns NULL until the window has accumulated at least 6 rows.

SELECT deptno, ename, sal,
       nth_value(sal, 6) OVER (PARTITION BY deptno ORDER BY sal) AS nth_value
FROM emp;

Result:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |
| 10         | MILLER     | 1300       | NULL       |   -- Window has 2 rows; NULL is returned.
| 10         | CLARK      | 2450       | NULL       |
| 10         | WELAN      | 2450       | NULL       |
| 10         | KING       | 5000       | 5000       |
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | NULL       |
| 30         | WARD       | 1250       | NULL       |
| 30         | TURNER     | 1500       | NULL       |
| 30         | ALLEN      | 1600       | NULL       |
| 30         | BLAKE      | 2850       | 2850       |
+------------+------------+------------+------------+

In department 10, the 6th row (ordered by sal) has a salary of 5000, so KING and JACCKA — the rows at and after position 6 — both return 5000. Department 20 never reaches 6 rows, so all rows return NULL.

Related functions

NTH_VALUE is a window function. For related functions and window specification details, see Window functions.