NTH_VALUE

NTH_VALUE returns the nth value of x from the first or last value in the window. Counting starts at 1. FIRST refers to the earliest row in the window (the row with the earliest value of rowtime. LAST refers to the latest row (usually the current value of rowtime).

Syntax

NTH_VALUE(x, n) [ <from first or last> ] [ <null treatment> ] over - window-definition

where:

<null treatment> := RESPECT NULLS | IGNORE NULL
<from first or last> := FROM FIRST | FROM LAST

Notes

Default is first. If <null treatment> is set to IGNORE NULLS, then the function will skip over nulls while counting.

If there are not enough rows in the window to reach nth value, function returns NULL.

See the topic WINDOW clause of the SELECT statement for more details on defining windows.