FIRST_VALUE and LAST_VALUE Explained Practically

1. What FIRST_VALUE and LAST_VALUE are meant for

FIRST_VALUE and LAST_VALUE are window functions that return the first or last value of a given column within a window, without reducing the number of result rows. Typical use cases are the starting price and ending price of a product in a price history, the first and last order of a customer within a time period, or the baseline value and the most recent value of a metric for a change calculation.

At first glance both functions seem symmetric: FIRST_VALUE fetches the value of the first row, LAST_VALUE the value of the last row within the window defined by PARTITION BY and ORDER BY. In practice, though, LAST_VALUE behaves fundamentally differently from what most developers expect, and without an additional adjustment it almost never returns the desired value.

This article explains why that is, how the correct frame clause fixes LAST_VALUE, how IGNORE NULLS interacts with both functions, and how FIRST_VALUE and LAST_VALUE are used in real reporting queries.

2. FIRST_VALUE: the straightforward case

FIRST_VALUE works intuitively and rarely causes problems in practice. With FIRST_VALUE(column) OVER (PARTITION BY group ORDER BY sort_column), the function returns, for every row, the value of the first row in the respective partition, sorted by the given column. The reason FIRST_VALUE works fine with the default frame: the default window frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and the first value of that range is always identical to the first value of the entire partition, regardless of the current row position.

A practical example: for every order of a customer, the price of their very first order should be shown, to make price changes over time visible. FIRST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date) delivers exactly that, without an explicit frame clause being necessary, because the default frame already produces the correct result for FIRST_VALUE.

-- FIRST_VALUE works correctly with the default frame
SELECT
    customer_id,
    order_date,
    order_amount,
    FIRST_VALUE(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS first_order_amount
FROM orders
ORDER BY customer_id, order_date;

-- customer_id | order_date | order_amount | first_order_amount
-- 1           | 2026-01-05 | 89.00        | 89.00
-- 1           | 2026-03-12 | 120.00       | 89.00
-- 1           | 2026-06-01 | 65.00        | 89.00

3. The frame trap: why LAST_VALUE is usually wrong

The most common mistake with window functions in SQL is assuming LAST_VALUE automatically returns the last value of a partition, symmetric to FIRST_VALUE. But the default window frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ends at the current row, not at the end of the partition. For LAST_VALUE this means the window frame moves along with every row, and the last value within that moving frame is always the value of the current row itself.

The result is sobering: without an adjusted frame clause, LAST_VALUE simply returns the value of the current row for every row, not the value of the last row in the partition. This is not a bug, it is exact default behavior per the SQL standard, but it contradicts the intuitive expectation of nearly every developer using LAST_VALUE for the first time, and it regularly leads to silently wrong reports.

-- WRONG: LAST_VALUE with the default frame just returns the current row
SELECT
    customer_id,
    order_date,
    order_amount,
    LAST_VALUE(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS last_order_amount_wrong
FROM orders
ORDER BY customer_id, order_date;

-- customer_id | order_date | order_amount | last_order_amount_wrong
-- 1           | 2026-01-05 | 89.00        | 89.00   <- equals current row
-- 1           | 2026-03-12 | 120.00       | 120.00  <- equals current row
-- 1           | 2026-06-01 | 65.00        | 65.00   <- equals current row
-- Expected: every row should show 65.00, the true last order amount

4. The fix: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

The correct fix for LAST_VALUE is an explicit frame clause that extends the frame to the entire partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. With that, the window frame for every row always covers all rows of the partition, regardless of the current position, and LAST_VALUE actually returns the last value in the order defined by ORDER BY.

It matters to set this explicit frame clause consistently for every LAST_VALUE call, regardless of whether the current test result happens to look correct. Especially with small test data sets with few rows per partition, the mistake often goes unnoticed because the last row happens to match the current row, only to break visibly in production with longer partitions.

-- RIGHT: explicit frame extends to the entire partition
SELECT
    customer_id,
    order_date,
    order_amount,
    FIRST_VALUE(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_order_amount,
    LAST_VALUE(order_amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM orders
ORDER BY customer_id, order_date;

-- customer_id | order_date | order_amount | first_order_amount | last_order_amount
-- 1           | 2026-01-05 | 89.00        | 89.00               | 65.00
-- 1           | 2026-03-12 | 120.00       | 89.00               | 65.00
-- 1           | 2026-06-01 | 65.00        | 89.00               | 65.00

5. Handling NULLs: IGNORE NULLS and RESPECT NULLS

If the target column contains NULL values, LAST_VALUE returns NULL by default whenever the last row of the partition carries NULL in that column. That is the intended default behavior, RESPECT NULLS, but it is unwanted in many reporting scenarios, for example when you're looking for the last known valid value of a metric, not literally the last row. PostgreSQL, Oracle and SQL Server support IGNORE NULLS for this, which skips NULL rows when determining the first or last value.

MySQL does not support IGNORE NULLS directly through version 8.0, so a workaround with COALESCE combined with an additional sort that pushes NULL values to the end, before LAST_VALUE is applied, helps here. It matters: IGNORE NULLS does not remove the need for the frame clause from section four, both adjustments are independent of each other and must be combined where needed.

-- IGNORE NULLS: skip NULL rows when finding the last known value
SELECT
    sensor_id,
    reading_time,
    temperature,
    LAST_VALUE(temperature) IGNORE NULLS OVER (
        PARTITION BY sensor_id
        ORDER BY reading_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_known_temperature
FROM sensor_readings
ORDER BY sensor_id, reading_time;
-- PostgreSQL, Oracle, SQL Server support IGNORE NULLS directly

6. Combining FIRST_VALUE and LAST_VALUE with PARTITION BY

PARTITION BY limits the range within which FIRST_VALUE and LAST_VALUE search to a logical group, for example all orders of a customer, all price changes of a product, or all readings of a sensor. Without PARTITION BY, the entire result set counts as a single partition, which is rarely what you want in practice. The combination of PARTITION BY and the correct frame clause is therefore the standard pattern for nearly every production use of LAST_VALUE.

Multiple PARTITION BY columns are also possible, for example PARTITION BY customer_id, product_category, to determine the first and last purchase price per customer and category separately. The frame clause does not need to be adjusted for that, it always applies to the group currently active through PARTITION BY, regardless of how many columns take part in the partitioning.

7. NTH_VALUE as a third variant

Alongside FIRST_VALUE and LAST_VALUE, a third, lesser known window function NTH_VALUE(column, n) exists, returning the value of the n-th row within the window. It is useful when neither the first nor the last, but a specific middle value is needed, for example a customer's third order to analyze early repeat purchases. Like LAST_VALUE, NTH_VALUE in most cases also needs a frame clause explicitly extended to the entire partition, otherwise n is only valid within the default frame up to the current row.

NTH_VALUE is used less often than FIRST_VALUE and LAST_VALUE, but it is available in all four major databases, PostgreSQL, MySQL 8, SQL Server and Oracle. It rounds out the toolset for situations where a report needs not just a beginning and an end, but also a specific intermediate value of an ordered sequence.

8. Use case: start and end value of a metric per group

A realistic use case is a price history report that shows, for every product, the original list price, the current price and the percentage change in a single row per product. FIRST_VALUE returns the original price, LAST_VALUE with the correct frame clause returns the current price, and a simple percentage calculation based on these two columns yields the change, all in a single query without a self-join.

A second common use case is filling gaps in time series, the so-called forward-fill pattern: LAST_VALUE with IGNORE NULLS and a frame clause limited to the current row, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, carries the last known non-NULL value forward for every row with missing data, for example with sporadically reported sensor readings.

-- Product price change report: first vs. last known price
SELECT DISTINCT
    product_id,
    FIRST_VALUE(price) OVER (
        PARTITION BY product_id ORDER BY changed_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS original_price,
    LAST_VALUE(price) OVER (
        PARTITION BY product_id ORDER BY changed_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS current_price
FROM price_history
ORDER BY product_id;

9. FIRST_VALUE and LAST_VALUE compared directly

The table shows the crucial structural difference: FIRST_VALUE happens to work correctly with the default frame because the first value of the default frame always matches the first value of the partition. LAST_VALUE and NTH_VALUE don't share that luck, because their target value can lie outside the moving default frame. Once you internalize this, you never forget the explicit frame clause with LAST_VALUE again.

A pragmatic piece of practical advice: instead of relying on implicit behavior, every use of LAST_VALUE and NTH_VALUE should spell out the frame clause explicitly, even if a code review could theoretically infer it from context. Explicit frame clauses are self-documenting and prevent a later refactoring step from accidentally reactivating the implicit default frame.

10. Summary

FIRST_VALUE and LAST_VALUE look symmetric at first glance, but behave fundamentally differently as long as no explicit frame clause is set. FIRST_VALUE already returns the correct first value of a partition with the SQL standard default frame, while LAST_VALUE without ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING merely returns the value of the current row. This frame trap is by far the most common mistake when using these two functions in practice.

IGNORE NULLS complements both functions wherever the first or last valid value is needed instead of the literal first or last value, independent of the frame clause. Combined with PARTITION BY and the correct frame clause, FIRST_VALUE and LAST_VALUE form a reliable tool for start-end comparisons, price history reports and forward-fill patterns in time series.

11. FAQ: FIRST_VALUE and LAST_VALUE