This page is a quick reference checkpoint for NTH VALUE in BigQuery: 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 value of value_expression at the Nth row of the current window frame, where N is a constant integer expression. Returns NULL if no such row. NULLs are included unless IGNORE NULLS is specified.
If this behavior feels unintuitive, the tutorial below explains the underlying pattern step-by-step.
NTH_VALUE(value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS]) OVER (window_clause)
SELECT NTH_VALUE(sales_amount, 3) OVER (PARTITION BY region ORDER BY sale_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_sale_in_region FROM sales_table;
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 BigQuery Documentation →Looking for more functions across all SQL dialects? Visit the full SQL Dialects & Window Functions Documentation.