NTH_VALUE Function
intermediatewindow-functions
2 min read
What is NTH_VALUE?
NTH_VALUE(column, n) returns the value from the Nth row of the window frame. It generalizes FIRST_VALUE (n=1) and can retrieve any positional value.
Syntax
SELECT column,
NTH_VALUE(column, 2) OVER (
PARTITION BY group_col ORDER BY sort_col
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS second_value
FROM table_name;When to Use
- Getting the 2nd, 3rd, or Nth value in a ranked group
- Comparing to a specific position within a partition
- Accessing the runner-up value after the first
Key Points
- N is 1-Based — NTH_VALUE(col, 1) is the same as FIRST_VALUE(col).
- Frame Matters — Like LAST_VALUE, use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to access all rows.
- NULL if N Exceeds Rows — If the frame has fewer than N rows, the result is NULL.
- PARTITION BY — Scopes the function to each group.
- Static N — N must be a constant or parameter, not a column reference.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
NTH_VALUE Function Challenge
Write a query that find the age of the second oldest student in each grade.
Expected result
Each student with the second-highest age in their grade group.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is NTH_VALUE?
Initializing database...Each run starts from fresh sample data.
More Examples
Third most expensive product per category
Show the price of the 3rd most expensive product per category.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What if N is greater than the number of rows?
NTH_VALUE returns NULL when the Nth row does not exist in the frame.
Can N be a column value?
No. N must be a positive integer constant or parameter expression, not a column reference.