nth_value ​

Returns the nth value in the current ordered window frame. The function selects a row position; it retains a NULL at that position.

Frame bounds determine which rows can be selected. Use an explicit frame when the result should cover a fixed preceding range or the whole partition.

Syntax

nth_value(value, n) OVER (
    [PARTITION BY expression, ...]
    ORDER BY expression, ...
    [ROWS | RANGE | GROUPS frame_bounds]
)

Arguments

• value — The expression evaluated at the selected row.
• n — The one-based position within the frame. It is evaluated once and converted to an integer; a converted position below 1 raises a SQL error.

Returned value The selected input value, preserving its native type. An empty frame or a frame with fewer than n rows returns NULL. Frame exclusions apply before selecting the position.

Examples Reading from a growing frame

The frame starts at the first sale and ends at the current row:

CREATE TABLE sales (day INTEGER, amount INTEGER);
INSERT INTO sales VALUES (1, 120), (2, 150), (3, 90);

SELECT day, amount,
       nth_value(amount, 2) OVER (
           ORDER BY day
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS selected_amount
FROM sales
ORDER BY day
FORMAT csv;

1,120,\N
2,150,150
3,90,150

See window functions for supported frame units and exclusions.