NTH_VALUE()

Syntax

How it works

NTH_VALUE(column, n) is a window function. It looks at the ordered set of rows in the current window frame and returns the value of column from the nth row, where n is counted from 1. It is the natural companion of FIRST_VALUE() and LAST_VALUE(): where those grab the first and last rows, NTH_VALUE() grabs the one you point at in between.

Because it is a window function it keeps every row of your result instead of collapsing them like an aggregate would. Each row is stamped with the same nth value from its partition, which is exactly what you want when you need the second highest price or the third earliest order date sitting next to every detail row for comparison.

It is available in PostgreSQL, MySQL 8.0 and later, and SQL Server 2022 and later. Older versions of SQL Server (2019 and earlier) do not support NTH_VALUE(), so on those you emulate it, usually with ROW_NUMBER() plus a self join or a conditional aggregate.

The one trap to remember is the window frame. When you add an ORDER BY inside OVER(), the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the frame grows one row at a time. That means early rows cannot yet see the nth row and return NULL. To read the nth value across the whole partition, widen the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This is the same caveat that trips people up with LAST_VALUE(). For deeper background see the window functions guide.

Examples

Second highest price in each category on every row

SELECT category, product, price,
       NTH_VALUE(price, 2) OVER (
         PARTITION BY category
         ORDER BY price DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_highest_price
FROM products;

category | product | price | second_highest_price
---------+---------+-------+---------------------
 Phones  | Pro     |   999 |                 799
 Phones  | Plus    |   799 |                 799
 Phones  | Mini    |   499 |                 799

Third earliest order date per customer

SELECT customer_id, order_id, order_date,
       NTH_VALUE(order_date, 3) OVER (
         PARTITION BY customer_id
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_order_date
FROM orders;

customer_id | order_id | order_date | third_order_date
------------+----------+------------+-----------------
         7 |     1001 | 2026-01-02 | 2026-02-11
         7 |     1002 | 2026-01-20 | 2026-02-11
         7 |     1003 | 2026-02-11 | 2026-02-11

Find the runner-up (second place) score

-- Bring the second best score next to every player
SELECT player, score,
       NTH_VALUE(player, 2) OVER (
         ORDER BY score DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS runner_up
FROM leaderboard
ORDER BY score DESC;

player | score | runner_up
-------+-------+----------
 Amina |    98 | Bilal
 Bilal |    91 | Bilal
 Carlos|    87 | Bilal

Default frame cuts off later rows (returns NULL early)

-- No explicit frame, so it defaults to CURRENT ROW.
-- The first row cannot see the 2nd row yet.
SELECT product, price,
       NTH_VALUE(price, 2) OVER (ORDER BY price DESC) AS second_price
FROM products;

product | price | second_price
--------+-------+-------------
 Pro    |   999 |       NULL
 Plus   |   799 |        799
 Mini   |   499 |        799

Common mistakes

-- Default frame stops at the current row, so the
-- nth value is NULL until the frame reaches row n
SELECT product, price,
       NTH_VALUE(price, 2) OVER (ORDER BY price DESC) AS second_price
FROM products;

-- Widen the frame to the whole partition
SELECT product, price,
       NTH_VALUE(price, 2) OVER (
         ORDER BY price DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_price
FROM products;

-- Expecting the 3rd row but counting from 0
SELECT NTH_VALUE(order_date, 0) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS wanted_third
FROM orders;

-- n is 1-based: the 3rd row is n = 3
SELECT NTH_VALUE(order_date, 3) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS third_date
FROM orders;

-- Fails on SQL Server 2019 and earlier:
-- 'NTH_VALUE' is not a recognized function name
SELECT NTH_VALUE(price, 2) OVER (
         ORDER BY price DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_price
FROM products;

-- Emulate it with ROW_NUMBER on older SQL Server
WITH ranked AS (
  SELECT price,
         ROW_NUMBER() OVER (ORDER BY price DESC) AS rn
  FROM products
)
SELECT (SELECT price FROM ranked WHERE rn = 2) AS second_price;

Performance

NTH_VALUE() needs an ordered window, so the engine sorts each partition once. If your PARTITION BY and ORDER BY columns are covered by an index in the right order, the optimiser can skip the sort and read rows already ordered, which is the single biggest win for large tables.

Widening the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is correct for reading the nth value across a partition and does not force a second sort; the whole partition is already in memory as one frame. Prefer ROWS over RANGE when you want exact row positions, since RANGE groups tied values together and can change which physical row counts as the nth.

If you only ever need one positioned value and do not need it stamped on every row, a ROW_NUMBER() filter (WHERE rn = n) reads less data than a full window pass. Reach for NTH_VALUE() when you specifically want that value alongside all the detail rows.

Interview questions

SELECT product, price,
       NTH_VALUE(price, 2) OVER (
         ORDER BY price DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_price
FROM products;