NTH_VALUE

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks > Functions > Built-in Functions >

NTH_VALUE

Previous pageReturn to chapter overviewNext page

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 lowest 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.