FIRST_VALUE, LAST_VALUE, and NTH_VALUE: Reading Values by Position

FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() reach into a window and pull back a value from a specific position within it: the first row, the last row, or the nth row. They sound simple, and FIRST_VALUE() is. But LAST_VALUE() is the single most misunderstood window function in SQL, because its answer depends entirely on a frame most people never realise is there. Understanding these three functions is really about understanding the frame they read from.

Value by Position, Not by Offset

These three belong to the family of value functions, functions that fetch a value from another row rather than computing something new. Where LAG() and LEAD() reach a relative distance from the current row (one row back, two rows forward), FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() reach an absolute position within the frame: the first row in view, the last row in view, the nth row in view. That phrase, "within the frame," is the whole story. These functions do not look at the partition as a whole; they look at whatever slice of it the window frame currently exposes, and the frame has a default you did not write.

All three are order-sensitive: if you want "first," "last," or "nth" to have a meaningful and reproducible interpretation, define an ORDER BY in the window specification. Each takes one expression to return; NTH_VALUE() also takes the position to fetch.

One mental model covers all three. PARTITION BY decides which rows belong together, ORDER BY sequences them, the frame decides which part of that sequence the current row can see, and the function simply reads one position of that frame:

The Sample Dataset

We use a small table of monthly revenue, read in calendar order. month is already unique, so ordering by it is deterministic on its own. revenue is not: February and March share 1200.00, and April and May share 1500.00. Whenever we order by revenue and the specific row matters, the id column serves as a tiebreaker. Those ties also matter for how the frame behaves, as we will see.

monthly_sales

FIRST_VALUE(): the First Row in the Frame

FIRST_VALUE() returns the value of the expression from the first row of the frame. Under the default frame, which starts at UNBOUNDED PRECEDING (the very start of the partition), that first row never changes as you move down the rows, so the result is stable on every row. This makes FIRST_VALUE() the easy, well-behaved member of the trio:

Every row reports 1000.00, January's revenue and the first row in the ordering. The default frame runs from the start of the partition through the current row, and because it always begins at the same place, "the first row in the frame" is always January. FIRST_VALUE() works as expected precisely because the default frame's start is fixed. As we are about to see, LAST_VALUE() is undone by the fact that the default frame's end is not.

FIRST_VALUE() with PARTITION BY

The real power of FIRST_VALUE() shows when you order within groups. For this example we use a small regional_sales table: three months of revenue for each of two regions, North and South (every row appears in the result below). Order each partition by revenue descending, and the first row of the frame becomes the top row of the group, a compact way to put "the best month in this region" on every row. Because the top value sits at the fixed start of the frame, the default frame is enough here:

Each region's rows now carry the month of its highest revenue: February for North, March for South. Ordering by revenue DESC puts the best month at the top of each partition, and FIRST_VALUE() reads it off. The rows are shown ordered by revenue within each region so the effect is easy to follow.

LAST_VALUE(): the Function That Surprises Everyone

You would expect LAST_VALUE() to be the mirror of FIRST_VALUE(): the last row of the partition, on every row. It is not, and this is the classic SQL gotcha. Run the obvious query:

The actual result gives each row its own revenue back, not the final 1800.00. The reason is the default frame. With ORDER BY present, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in engines such as PostgreSQL, MySQL, SQL Server, SQLite, and Oracle. Because month is unique, that frame ends exactly at the current row, so "the last row in the frame" is the current row itself, and LAST_VALUE() faithfully returns it. The function is not broken; it is answering a question about a frame that stops where you are standing.

The fix is to say what you actually mean: a frame that extends all the way to the end of the partition.

With the frame extended to UNBOUNDED FOLLOWING, every row can see through to the last row of the partition, and LAST_VALUE() returns the genuine final revenue of 1800.00 everywhere. The rule to remember is about intention: when you mean "the last value in the entire partition," say so with a frame ending in UNBOUNDED FOLLOWING.

When the Ordering Has Ties

There is one more subtlety hiding in that default frame. It is a RANGE frame, and with RANGE, CURRENT ROW does not mean the current physical row: it means the current row and all of its peers, the rows that tie on the ORDER BY value. Ordering by the unique month column hides this. Order by revenue instead, and the effect becomes visible and measurable.

The clearest way to see it is to count how many rows are actually inside the frame under each mode, using COUNT(*). Unlike asking "which row is last," a row count does not depend on how the engine breaks ties within a peer group, so the comparison is fully deterministic:

rows_count grows by exactly one at each row, because the id tiebreaker makes every row its own peer, and ROWS simply counts physical position. range_count tells a different story: id 2 and id 3, the two 1200.00 rows, both report 3, because RANGE treats them as one indivisible peer group and the frame jumps straight from 1 to 3 without stopping at 2. The same happens to the 1500.00 pair, jumping from 3 to 5. This is the concrete shape of "peers share a frame": the frame boundary moves in steps of a whole peer group, not one row at a time.

Now put a value function in that same frame instead of a count, and the ambiguity from the section above becomes visible:

For February, last_month may come back as 2024-03 rather than 2024-02, because, as the table above just showed, March sits inside the same three-row peer group. Worse, which of the two tied rows counts as "last" is not guaranteed at all: ORDER BY revenue says nothing about how February and March are ordered relative to each other, so the engine is free to put either one at the end of the peer group.

NTH_VALUE(): the Nth Row in the Frame

NTH_VALUE(expression, n) generalises the idea: it returns the expression from the nth row of the frame. The position n is 1-based, so NTH_VALUE(x, 1) reads the same row as FIRST_VALUE(x), and it must be a positive integer: engines reject 0 or negative values rather than interpreting them. Like LAST_VALUE(), it reads the frame, so it is subject to the same default-frame effect, and it additionally returns NULL whenever the frame does not yet contain n rows. Here we ask for the third month's revenue, with the frame extended so every row can see it:

The third row in calendar order is March, at 1200.00, so every row reports 1200.00. Because the frame spans the whole partition, the "third row" is the same for all of them.

Watch what happens with the default frame instead. Now the frame grows one row at a time, and until it contains at least three rows, there is no third row to return:

January and February return NULL: their frames (one row, then two rows) have no third element yet. From March on, the frame holds at least three rows and NTH_VALUE() settles on March's 1200.00. This is the same default-frame lesson as LAST_VALUE(), with an extra wrinkle: an nth position that the frame has not reached yields NULL, not an error.

The Three Together

Running all three with the same extended frame shows how they carve different positions out of the same window:

All three read the same frame here (start through the very end of the partition), so each simply picks off a different position within it. Each row now carries the first (1000.00), third (1200.00), and last (1800.00) revenues of the whole series side by side.

A Practical Use Case: Comparing Against the Best

Once the first or last value of a group sits on every row, comparing each row against it is ordinary arithmetic. A common request is "how far is each month from the best month in the series?" Combine FIRST_VALUE() over a descending order with a subtraction:

Ordering by revenue DESC places the best month (June, 1800.00) at the top of the frame, so FIRST_VALUE() reads 1800.00 onto every row, and gap_to_best measures how far each month falls short of it. June's gap is zero because it is the best; January trails by 800.00. Because FIRST_VALUE() reads the fixed start of the frame, the default frame needs no extension here.

Note that this measures the gap to the best month of the whole series, not the best month so far. A running "best so far" is a different question, and it is a job for MAX() over a growing frame rather than FIRST_VALUE():

The distinction is worth internalising: FIRST_VALUE() returns whatever row happens to sit at the start of the frame, so it only equals a maximum when you order the frame by that same value. It is not a general substitute for MAX().

Skipping NULLs with IGNORE NULLS

The SQL standard defines an optional { RESPECT | IGNORE } NULLS modifier for these functions, but support varies significantly by database, so check your engine's documentation before using it. Where it exists, RESPECT NULLS is the default and treats a NULL-valued row like any other: if the first row of the frame has a NULL in the target column, FIRST_VALUE() returns NULL. IGNORE NULLS instead skips over NULL-valued rows and returns the first (or last, or nth) non-null value it finds:

This is genuinely useful for "last known value" style queries, carrying the most recent non-null reading forward over gaps in a series. The support picture is uneven: Oracle and Amazon Redshift have long offered it; SQL Server added it for FIRST_VALUE() and LAST_VALUE() in SQL Server 2022; PostgreSQL added it in version 19, and in earlier versions these functions always behave as RESPECT NULLS; MySQL accepts only RESPECT NULLS; and SQLite does not support the modifier. On engines without it, there is a portable pattern that produces the same result, shown next.

A Portable Alternative: Carrying the Last Known Value

Because IGNORE NULLS is not universal, it is worth knowing a pattern that gives the same "last known value" result on every engine. Consider a version of the monthly figures where some months were never recorded:

monthly_sales_gaps

The goal is to fill each gap with the most recent revenue that was actually recorded. The trick takes two steps. First, a running COUNT(revenue): because COUNT() of a column skips NULLs, the count only increases on rows that have a value, so every NULL row inherits the count of the recorded row before it. That count works as a group label. Second, each group contains exactly one non-null revenue, so MAX() over the group returns it:

January starts group 1. February and March have no revenue, so the running count stays at 1 and they join January's group, where the only recorded value is 1000.00. April's value moves the count to 2 and starts a new group, which May joins, and June starts group 3. The explicit ROWS frame on the running count keeps the query portable to engines that require one. One edge case: if a series begins with NULLs, those rows fall into group 0, which has no recorded value, so their last_known stays NULL. There is simply nothing earlier to carry forward.

On engines that support it, this is the same result as LAST_VALUE(revenue) IGNORE NULLS OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The two-step version is longer, but it runs unchanged everywhere.

Common Pitfalls

• Assuming LAST_VALUE() returns the partition's last row. By default it returns the current row, because the default frame ends at CURRENT ROW. Add an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame to get the true last value.
• Forgetting that NTH_VALUE() can return NULL. If the frame does not yet contain n rows, the result is NULL, not an error. With the default growing frame the first n − 1 rows will be NULL; extend the frame if you want the nth value on every row.
• Omitting ORDER BY inside OVER. Without an ordering, "first," "last," and "nth" have no defined meaning. These functions need an ORDER BY to be sensible.
• Ordering by a non-unique column and expecting a stable pick. When rows tie on the ORDER BY value, which one counts as "first," "last," or "nth" is not guaranteed, and under the default RANGE frame the frame itself extends through all the peers of the current row. Add a unique tiebreaker (such as id) and a ROWS frame when the specific row matters.
• Assuming IGNORE NULLS is portable. Its availability and syntax differ across engines; verify before relying on it, or use the portable COUNT()/MAX() pattern instead.

Cross-Database Support

The three functions are widely available, but not uniformly. Compatibility details here refer to the current documentation for each engine at the time of writing:

The most notable gap is that SQL Server does not implement NTH_VALUE(); there you emulate it, for example by numbering rows with ROW_NUMBER() in a subquery and filtering on the position you want. Default frames also deserve care: the RANGE ... CURRENT ROW default described in this article applies to PostgreSQL, MySQL, SQL Server, SQLite, and Oracle, but frame rules are exactly where dialects diverge, and some engines, Amazon Redshift among them, are stricter about requiring an explicit frame. Spelling the frame out is the portable choice.

Choosing Among the Three

The trio reads three fixed positions out of the frame, and the frame is the one thing that ties them together:

Decide what you mean, then write the frame that says it. These functions are not quirky; they are simply honest about the frame they read.