NTH_VALUE [analytic]

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

Behavior type

Syntax

NTH_VALUE ( expression, row-number [ IGNORE NULLS ] ) OVER (
    [ window-frame-clause ]
    [ window-order-clause ]
    [ 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.

Examples

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 *n*th 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 *n*th 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)