<< Click to Display Table of Contents >>

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


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


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



<null treatment> := RESPECT NULLS | IGNORE NULL

<from first or last> := FROM FIRST | FROM LAST



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.