This page is a quick reference checkpoint for NTH VALUE in ORACLE: 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.
Behavior: Returns the value of expr from the nth row in the window. Direction defaults to FROM FIRST. When ORDER BY is present, default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which may cause unexpected results unless ROWS-based frames are specified.
If this behavior feels unintuitive, the tutorial below explains the underlying pattern step-by-step.
Syntax: NTH_VALUE(expr, n [, from_clause]) OVER (analytic_clause). n must be a positive integer; FROM FIRST or FROM LAST optionally specifies direction.
SELECT department_id, hire_date, salary, NTH_VALUE(salary, 2) OVER (PARTITION BY department_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_sal FROM employees;
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 ORACLE Documentation →Looking for more functions across all SQL dialects? Visit the full SQL Dialects & Window Functions Documentation.