NTH_VALUE in PostgreSQL: the n-th Window Value and Second-Highest per Group

NTH_VALUE(expr, n) is a window function that returns expr from the n-th row of the window frame. It sounds trivial, yet this is exactly where people get burned: by default the frame is clipped at the current row, and "the second-highest value in the group" silently becomes NULL.

What NTH_VALUE computes

The function looks at the current window frame, counts to the n-th row (one-based), and pulls expr from it. If no row with that position exists in the frame, the result is NULL.

SELECT
    id,
    amount,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM orders;

Key details:

• n is the position inside the frame, not inside the whole partition; the frame decides everything.
• Numbering starts at 1: NTH_VALUE(x, 1) is equivalent to FIRST_VALUE(x).
• n must be a positive integer; you can make it an expression, but it cannot reference per-row columns.

For tasks like "second-highest" you almost always want a frame that spans the whole partition, as in the example above.

The frame gotcha: same story as LAST_VALUE

The moment a window gets an ORDER BY, its default frame becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. So from the first row of a partition the "second-highest" is not yet visible: the frame holds a single row, and NTH_VALUE(amount, 2) returns NULL.

-- WRONG: default frame stops at the current row
SELECT
    id,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
    ) AS maybe_null
FROM orders;

The fix is the same as for LAST_VALUE: widen the frame explicitly to the whole partition.

-- RIGHT: widen the frame to the whole partition
SELECT
    id,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest
FROM orders;

Gotcha: FIRST_VALUE only "just works" under the default frame by accident, because the first row is always in the frame. NTH_VALUE and LAST_VALUE are frame-sensitive, so almost always spell out ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

FROM FIRST / FROM LAST and IGNORE NULLS

The SQL standard describes two handy ideas: count the n-th row from the end of the frame with FROM LAST, and skip empty values with IGNORE NULLS. They are worth recognising because they show up in the documentation of several engines and in portable SQL.

-- second value counting from the end of the frame
SELECT
    id,
    NTH_VALUE(amount, 2) FROM LAST OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_lowest
FROM orders;

-- second non-null status change, skipping NULLs
SELECT
    id,
    NTH_VALUE(status, 2) IGNORE NULLS OVER (
        PARTITION BY user_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_status
FROM orders;

Important caveat for PostgreSQL: the standard FROM FIRST/FROM LAST and IGNORE NULLS options are not reliable, portable PostgreSQL syntax. PostgreSQL's normal behaviour counts from the start of the frame and respects NULLs. When you need "the second non-empty value", the usual approach is a subquery: filter the empty rows out first, then apply ROW_NUMBER or NTH_VALUE to the cleaned set.

Second-highest per group

The classic task is "the second-largest order for each user". NTH_VALUE delivers it in one line, but watch out for ties: with equal amount values the function returns the value of the second physical row, not the second distinct value.

SELECT DISTINCT
    user_id,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY user_id
        ORDER BY amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_highest_order
FROM orders;

If you really want the second distinct level (tolerant of ties), reach for DENSE_RANK:

SELECT user_id, amount AS second_highest_distinct
FROM (
    SELECT
        user_id,
        amount,
        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rnk
    FROM orders
) s
WHERE rnk = 2;

This keeps ties honest: every row with the top amount gets rank 1, and the next distinct level is rank 2.

Differences in other databases

• MySQL (8.0+) has NTH_VALUE(expr, n), but support for the extra options such as FROM FIRST/LAST and IGNORE NULLS is version-dependent; in practice a ROW_NUMBER subquery is often simpler and clearer. The default-frame trap is exactly the same here.
• ClickHouse has no dedicated NTH_VALUE. The equivalent is built with groupArray plus indexing or arrayElement, or with row_number() in a subquery. The "second-highest" logic usually looks like this:

-- ClickHouse: second highest per user via array indexing
SELECT
    user_id,
    arrayElement(arraySort(x -> -x, groupArray(amount)), 2) AS second_highest
FROM orders
GROUP BY user_id;

NTH_VALUE is a precise tool for "the n-th value in the window", but it lives and dies by the frame. Widen the frame explicitly, remember the duplicates, keep DENSE_RANK handy for the "second distinct" case, and verify the from-the-end and skip-NULL options against your specific dialect.