The nth_value function can support some additional syntax "from last" and "from first". This is supported by columnstore and most other databases with window functions support:
NTH_VALUE(column, n) selects the value of the column for the nth row in the window. FROM FIRST says select the nth from the beginning of the window. FROM LAST says select the nth counting backwards from the end of the WINDOW. The functions FIRST_VALUE and LAST_VALUE are specializations of each of these with n=1.
For the examples, four and ten are integer columns of tenk1.
Here's a statement without FROM LAST (the default is FROM FIRST)
SELECT NTH_VALUE(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
Here it is with FROM LAST
SELECT NTH_VALUE(four, 0) FROM LAST OVER (ORDER BY ten), ten, four FROM tenk1;
RESPECT NULLS says to select NULL as the value if it happens to be in the nth row. IGNORE NULLS says to not count rows that are NULL. The default is RESPECT NULLS.
For the following sequence:
As far as NTH_VALUE is concerned, if IGNORE NULLS, then there are only two rows in the window.
SELECT NTH_VALUE(four, 0) FROM LAST IGNORE NULLS OVER (ORDER BY ten), ten, four FROM tenk1;