SQL NTH_VALUE Window Function Explained

A product analyst needs to benchmark each product's price against the second-cheapest product in its category. MIN gives the cheapest. But there's no built-in function for "second." That's exactly the gap NTH_VALUE fills.

How NTH_VALUE Works

NTH_VALUE is a window function that returns the value from a specific position in the ordered window frame. You tell it which column to read and which position to return. SQL does the rest.

Basic syntax:

SELECT
    column_name,
    NTH_VALUE(column_name, n) OVER (
        PARTITION BY group_column
        ORDER BY sort_column
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS nth_val
FROM table_name;

Three things to note. First, n is the position you want, starting at 1. Second, ORDER BY inside OVER defines what "position 1, position 2" and so on mean. Third, the frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is important and covered in the next section.

Here's a working example on the products table. This returns each product alongside the price of the second-cheapest product in the same category:

SELECT
    p.product_id,
    p.product_name,
    p.category_id,
    p.price,
    NTH_VALUE(p.price, 2) OVER (
        PARTITION BY p.category_id
        ORDER BY p.price ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_cheapest
FROM products p;

Every product in a category sees the same second_cheapest value. If a category has only one product, second_cheapest returns NULL because position 2 doesn't exist.

Why the Frame Clause Matters

This is the part that catches most people. The default window frame in SQL is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That means the frame only covers rows up to and including the current row.

If you're on position 1 in the sort order and you ask for NTH_VALUE(col, 3), position 3 hasn't been reached yet. The result is NULL.

Without the frame clause:

-- missing frame: rows before position n return NULL
SELECT
    p.product_id,
    p.price,
    NTH_VALUE(p.price, 3) OVER (
        PARTITION BY p.category_id
        ORDER BY p.price ASC
    ) AS third_cheapest
FROM products p;

The first two rows in each partition return NULL for third_cheapest because the frame hasn't reached position 3 yet. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and every row sees the same complete partition, so the result fills in correctly.

This is the same issue that affects [LAST_VALUE](/learn/sql-first-value-last-value-in-window-function). Any window function that needs to see rows after the current position requires this frame clause.

Using NTH_VALUE Without PARTITION BY

Drop PARTITION BY and NTH_VALUE operates across the entire result set. Every row gets the value from position n in the global sort order.

This returns every order alongside the total_amount of the third-largest order across all orders:

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    NTH_VALUE(o.total_amount, 3) OVER (
        ORDER BY o.total_amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS third_highest_order
FROM orders o;

Every row shows the same third_highest_order value because the partition is the entire table.

Choosing the Position and Sort Direction

The position number and the sort direction work together. To get the second highest, sort DESC and use position 2. To get the second lowest, sort ASC and use position 2.

This returns each employee's salary alongside the second-highest salary in their department:

SELECT
    e.employee_id,
    e.department,
    e.salary,
    NTH_VALUE(e.salary, 2) OVER (
        PARTITION BY e.department
        ORDER BY e.salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_dept_salary
FROM employees e;

Sort ASC instead and NTH_VALUE(e.salary, 2) would give you the second-lowest salary in the department.

How NTH_VALUE Connects to Other Concepts

NTH_VALUE is part of the same family as [FIRST_VALUE and LAST_VALUE](/learn/sql-first-value-last-value-in-window-function). FIRST_VALUE is effectively NTH_VALUE at position 1. LAST_VALUE with the correct frame is NTH_VALUE at the final position. If you need a specific non-boundary position, NTH_VALUE is the right choice.

To filter rows based on the result, for example to return only products priced at the second-cheapest level in their category, wrap the query in a [subquery or CTE](/learn/sql-cte-common-table-expressions) and apply WHERE in the outer query.

Common Mistakes

1. Omitting the frame clause and getting NULLs

Without ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, rows before position n return NULL because the default frame ends at the current row.

-- wrong: rows before position 2 return NULL
SELECT
    p.product_id,
    p.price,
    NTH_VALUE(p.price, 2) OVER (
        PARTITION BY p.category_id
        ORDER BY p.price ASC
    ) AS second_cheapest
FROM products p;

Add the full frame clause so every row in the partition can see all positions.

-- correct
SELECT
    p.product_id,
    p.price,
    NTH_VALUE(p.price, 2) OVER (
        PARTITION BY p.category_id
        ORDER BY p.price ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_cheapest
FROM products p;

2. Using position 0 or a negative number

Positions in NTH_VALUE start at 1. Passing 0 or a negative number causes an error.

-- wrong: position 0 is not valid
SELECT
    e.employee_id,
    NTH_VALUE(e.salary, 0) OVER (
        PARTITION BY e.department
        ORDER BY e.salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS result
FROM employees e;

Use position 1 for the first row, 2 for the second, and so on.

-- correct: position 1 returns the first row's value
SELECT
    e.employee_id,
    NTH_VALUE(e.salary, 1) OVER (
        PARTITION BY e.department
        ORDER BY e.salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS highest_dept_salary
FROM employees e;

3. Filtering on the NTH_VALUE alias in WHERE

Window functions run after WHERE, so the alias isn't available at that point in the query.

-- wrong
SELECT
    p.product_id,
    p.price,
    NTH_VALUE(p.price, 2) OVER (
        PARTITION BY p.category_id
        ORDER BY p.price ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_cheapest
FROM products p
WHERE price = second_cheapest;

Wrap the query in a subquery and filter in the outer SELECT.

-- correct
SELECT * FROM (
    SELECT
        p.product_id,
        p.price,
        NTH_VALUE(p.price, 2) OVER (
            PARTITION BY p.category_id
            ORDER BY p.price ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS second_cheapest
    FROM products p
) t
WHERE price = second_cheapest;

Quick Recap

• NTH_VALUE(column, n) returns the value from the nth row in the window frame, based on the ORDER BY you define inside OVER.
• Positions start at 1. There is no position 0.
• The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is required whenever you need rows after the current position to be visible.
• Without the frame clause, rows before position n return NULL because the default frame ends at the current row.
• Sort direction controls meaning: DESC with position 2 gives the second-highest value; ASC with position 2 gives the second-lowest.
• To filter on the result, use a subquery or [CTE](/learn/sql-cte-common-table-expressions) and apply WHERE in the outer query.

SELECT
    e.employee_id,
    e.department,
    e.salary,
    NTH_VALUE(e.salary, 2) OVER (
        ORDER BY e.salary ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_lowest_salary
FROM employees e;

SELECT
    e.employee_id,
    e.department,
    e.salary,
    NTH_VALUE(e.salary, 2) OVER (
        PARTITION BY e.department
        ORDER BY e.salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS dept_second_highest
FROM employees e;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    NTH_VALUE(pay.amount, 3) OVER (
        PARTITION BY pay.method
        ORDER BY pay.amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS third_largest_method_payment
FROM payments pay;

SELECT
    pay.payment_id,
    pay.method,
    pay.payment_date,
    pay.amount,
    NTH_VALUE(pay.amount, 2) OVER (
        PARTITION BY pay.method
        ORDER BY pay.amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_method_amount
FROM payments pay
WHERE pay.status = 'completed'
ORDER BY pay.method, pay.payment_date;

SELECT * FROM (
    SELECT
        pay.payment_id,
        pay.payment_date,
        pay.amount,
        NTH_VALUE(pay.amount, 3) OVER (
            ORDER BY pay.amount DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS third_highest_amount
    FROM payments pay
) t
WHERE amount = third_highest_amount;