NTH_VALUE [Analytic]

Returns the value evaluated at the row that is the nth row of the window (counting from 1). If the specified row does not exist, NTH_VALUE returns NULL.

Behavior Type

Immutable

Syntax

NTH_VALUE ( expression, row‑number [ IGNORE NULLS ] ) OVER (
... [ window-frame-clause ] 
... [ Window Order Cause ]
... [ window-frame-clause ] )

Parameters

expression

Expression to evaluate. The expression can be a constant, column name, nonanalytic function, function expression, or expressions that include any of these.

row‑number Specifies the row to evaluate, where row‑number evaluates to an integer ≥ 1.
IGNORE NULLS

Specifies to return the first non-NULL value in the set, or NULL if all values are NULL.

OVER()

See Analytic Functions.

Example

In the following example, for each tuple (current row) in table t1, the window frame clause defines the window as follows:

ORDER BY b ROWS BETWEEN 3 PRECEDING AND CURRENT ROW

For each window, n for nth value is a+1. a is the value of column a in the tuple.

NTH_VALUE returns the result of the expression b+1, where b is the value of column b in the nth row, which is the a+1 row within the window.

=> SELECT * FROM t1 ORDER BY a;
 a | b
---+---- 
 1 | 10
 2 | 20
 2 | 21
 3 | 30
 4 | 40
 5 | 50
 6 | 60
(7 rows)
=> SELECT NTH_VALUE(b+1, a+1) OVER 
     (ORDER BY b ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM t1;
 ?column?
----------


       22
       31



(7 rows)