SQL NTH_VALUE

What is SQL NTH_VALUE?

Returns the value of the nth row in a window frame.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL NTH_VALUE: PostgreSQL 9.4+, Oracle 12c+, SQL Server 2022+, MySQL 8.0+, Snowflake, BigQuery, DuckDB

SQL NTH_VALUE Full Explanation

NTH_VALUE is a ANSI SQL window function that returns the value of the nth row in the defined window frame, relative to the current row. Unlike FIRST_VALUE or LAST_VALUE that always fetch the first or last row of the frame, NTH_VALUE lets you pick any position by supplying an integer n. The function is computed after the ORDER BY clause within the OVER clause has been applied, and respects the frame mode (RANGE or ROWS) and boundaries. If the requested nth row lies outside the current frame, the result is NULL. Because the function is evaluated once per row, performance considerations are similar to other window functions – indexes on the ORDER BY columns help. n must be a positive integer constant or expression; zero or negative values raise an error.

SQL NTH_VALUE Syntax

NTH_VALUE ( value_expression , n )
  OVER (
    [PARTITION BY partition_expression_list]
    ORDER BY sort_expression_list
    [ROWS | RANGE BETWEEN frame_start AND frame_end]
  )

SQL NTH_VALUE Parameters

value_expression - Any valid column or expression whose nth value you need.
n - Positive INTEGER specifying the row position to return (1 is the first row).
partition_expression_list - Optional. Divides the result set into partitions.
sort_expression_list - Defines row order inside each partition.
frame_start, frame_end - Optional frame boundaries (e.g., UNBOUNDED PRECEDING, CURRENT ROW, n PRECEDING/FOLLOWING).

Example Queries Using SQL NTH_VALUE

-- Return the third highest salary within each department
SELECT dept_id,
       emp_id,
       salary,
       NTH_VALUE(salary, 3) OVER (
         PARTITION BY dept_id
         ORDER BY salary DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       )           AS third_highest_salary
FROM employees;

-- Compare current order amount to the 5th order amount company-wide
SELECT order_id,
       order_total,
       NTH_VALUE(order_total, 5) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       )           AS fifth_order_total
FROM orders;

Expected Output Using SQL NTH_VALUE

Each row gains an additional column containing the nth value specified
If fewer than n rows exist in the frame, the column shows NULL

Use Cases with SQL NTH_VALUE

Ranking reports where you need the 3rd or 5th value, not just min or max
Threshold calculations (e.g., compare current metric to the 10th value to detect outliers)
Business rules that depend on a specific position in an ordered set
Generating reference values for percentiles when combined with ordered frames

Common Mistakes with SQL NTH_VALUE

Forgetting ORDER BY inside the window clause, which makes row order undefined
Passing n = 0 or a negative number, which raises an error
Expecting NTH_VALUE to look past the frame boundaries when a restrictive frame is defined
Omitting ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and then wondering why NULLs appear (frame defaults to BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

First Introduced In

SQL:2008 standard (optional feature T611)

Frequently Asked Questions

What is the difference between NTH_VALUE and LAG?

LAG returns a value offset from the current row by a specified number of rows, regardless of frame boundaries. NTH_VALUE returns the value of the nth row of the window frame, which can be anywhere relative to the current row.

Can I use a column instead of a constant for n?

Most databases require n to be an integer literal or bind parameter. Using a column reference is not allowed and results in a syntax error.

How do I get the 2nd value without NULLs appearing?

Ensure your frame includes all rows by adding `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`. Otherwise, when the current row is earlier than the nth position, NTH_VALUE returns NULL.

Does NTH_VALUE work with DISTINCT?

No. DISTINCT cannot be applied inside a window function. Filter or aggregate your data in a subquery first, then apply NTH_VALUE.