This page is a quick reference checkpoint for NTH VALUE in Snowflake: behavior, syntax rules, edge cases, and a minimal example; plus the official vendor documentation.
NTH_VALUE returns the value from the nth row in the window frame.
Returns the nth value (up to 1000) within an ordered group of values; default window frame for ranking functions is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Input n cannot exceed 1000 and the ORDER BY clause is required.
If this behavior feels unintuitive, the tutorial below explains the underlying pattern step-by-step.
NTH_VALUE( <expr> , <n> ) [ FROM { FIRST | LAST } ] [ { IGNORE | RESPECT } NULLS ] OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] )
SELECT region, amount, NTH_VALUE(amount, 2) OVER (PARTITION BY region ORDER BY amount ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_amount FROM sales;
If you came here to confirm syntax, you’re done. If you came here to get better at window functions, choose your next step.
NTH VALUE is part of a bigger window-function pattern. If you want the “why”, start here: First Last Nth Value
For the authoritative spec, use the vendor docs. This page is the fast “sanity check”.
View Snowflake Documentation →Looking for more functions across all SQL dialects? Visit the full SQL Dialects & Window Functions Documentation.