SQL Window Function FIRSTVALUE and LASTVALUE

In the world of data analysis, comparing individual records to the "bookends" of a dataset—the first and last values within a logical group—is a fundamental task. SQL's FIRST_VALUE() and LAST_VALUE() window functions are powerful tools designed specifically for this purpose. They allow you to peer across ordered partitions of data without collapsing rows, enabling sophisticated analyses like tracking progress from a starting point, identifying final statuses, or calculating ranges. Mastering these functions, and crucially understanding the window frame that controls their scope, is key to unlocking accurate and insightful queries for trend analysis, data cleaning, and performance benchmarking.

Core Concept: Accessing Boundary Values in a Partition

At their core, FIRST_VALUE() and LAST_VALUE() retrieve the value from the first or last row of a defined window frame. A window function performs a calculation across a set of table rows that are somehow related to the current row. This is different from an aggregate function, which returns a single value for a group of rows.

The basic syntax for these functions is:

FIRST_VALUE(column_name) OVER (
    PARTITION BY partition_column
    ORDER BY order_column
    [frame_specification]
)

LAST_VALUE() uses the same structure. The PARTITION BY clause divides the data into groups; the function is applied independently to each partition. The ORDER BY clause is essential, as it determines the sequence of rows—what constitutes "first" and "last." Without a correctly specified frame, LAST_VALUE() can behave counterintuitively, a pitfall we will explore in detail.

Consider a table sales with region, sale_date, and revenue. To find the first revenue figure for each region in chronological order, you would write:

SELECT
    region,
    sale_date,
    revenue,
    FIRST_VALUE(revenue) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) as first_revenue_in_region
FROM sales;

This query adds a new column showing the revenue from the earliest sale date for that region on every row, allowing for easy comparison of each day's performance to the region's starting point.

The Critical Role of Window Frame Specification with ROWS BETWEEN

The default window frame for functions with an ORDER BY clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For LAST_VALUE(), this default is almost never what you intend. Because the frame ends at the current row, LAST_VALUE() will simply return the value from the current row, not the last row in the entire partition.

This is where the ROWS BETWEEN clause for frame specification becomes mandatory. To get the true last value in a partition, you must explicitly define the frame to cover all rows from the first to the last in that partition.

The correct syntax for a reliable LAST_VALUE() is:

SELECT
    region,
    sale_date,
    revenue,
    LAST_VALUE(revenue) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last_revenue_in_region
FROM sales;

The clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING defines the window frame as all rows in the partition, from the first to the last, relative to the current row's position in the order. Now, last_revenue_in_region will correctly show the revenue from the most recent sale date for that region on every row.

You can define other frames. For example, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING would create a three-row sliding window, and LAST_VALUE() within that frame would return the value from the row one step ahead of the current row.

Expanding to NTH_VALUE for Arbitrary Positions

While FIRST_VALUE() and LAST_VALUE() handle the boundaries, SQL provides NTH_VALUE(column_name, N) to retrieve the value from an arbitrary position within the window frame. Its behavior is directly analogous to LAST_VALUE(): it respects the defined window frame and requires the same careful use of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to find the partition's Nth row instead of the frame's Nth row.

For instance, to find the second sale recorded in each region:

SELECT
    region,
    sale_date,
    revenue,
    NTH_VALUE(revenue, 2) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as second_revenue_in_region
FROM sales;

Note that for rows where N is greater than the number of rows in the partition (e.g., asking for the 5th value in a partition with only 3 rows), NTH_VALUE() returns NULL.

Practical Applications: From Data Cleaning to Trend Analysis

These functions move beyond theory to solve common analytical problems. One key application is filling gaps or imputing values in a sequence. If you have sporadic sensor readings where you want to carry the last known value forward, LAST_VALUE() with an IGNORE NULLS option (available in databases like PostgreSQL) is perfect:

SELECT
    reading_time,
    sensor_value,
    LAST_VALUE(sensor_value IGNORE NULLS) OVER (
        ORDER BY reading_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as imputed_value
FROM sensor_log;

Another powerful use case is comparing to extremes. For instance, calculating the difference or percentage change between the current value and the first or last value in a period is straightforward.

SELECT
    employee_id,
    month,
    performance_score,
    FIRST_VALUE(performance_score) OVER (
        PARTITION BY employee_id
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as first_score,
    performance_score - FIRST_VALUE(performance_score) OVER (
        PARTITION BY employee_id
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as improvement_from_start
FROM reviews;

Finally, they are instrumental in trend analysis and identifying peaks or troughs. By combining FIRST_VALUE() and LAST_VALUE(), you can quickly compute the total growth or decline across a defined period for each partitioned entity, such as a stock's price change from the opening to the closing of each day.

Common Pitfalls

1. Ignoring Frame Specification for LAST_VALUE(): As stressed earlier, the most common and critical error is using LAST_VALUE() without an explicit frame. Always remember: ORDER BY alone is insufficient. You must append ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the actual last row in the partition.

• Incorrect: LAST_VALUE(revenue) OVER (PARTITION BY region ORDER BY sale_date)
• Correct: LAST_VALUE(revenue) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

1. Misunderstanding PARTITION BY: Forgetting the PARTITION BY clause will apply the function over the entire result set as one big group. This is often a logical error when you intend to analyze groups separately. Always verify that your partition columns correctly define the boundaries for your "first" and "last" logic.

1. Confusing ORDER BY Logic: The definition of "first" and "last" is entirely dependent on your ORDER BY clause. Ordering by an incorrect column (e.g., id instead of date) or in the wrong direction (DESC instead of ASC) will yield meaningless boundary values. Carefully consider the business logic behind the sequence.

1. Assuming NTH_VALUE without a Full Frame: The pitfall for LAST_VALUE() applies equally to NTH_VALUE(). To find the Nth row of the partition, you need the full unbounded frame. Using the default frame will only find the Nth row within the limited window up to the current row, which is rarely useful.

Summary

• FIRST_VALUE() and LAST_VALUE() are essential SQL window functions for retrieving boundary values from ordered partitions of data, enabling row-by-row comparison to endpoints.
• The window frame, specified with ROWS BETWEEN, is critical. The default frame causes LAST_VALUE() to return the current row's value; you must use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value in the partition.
• NTH_VALUE() extends this capability to retrieve values from any position within a frame and requires the same careful frame specification as LAST_VALUE().
• These functions have powerful practical applications, including imputing missing data by carrying the last known value forward, calculating changes from start or end points, and performing foundational trend analysis.
• To avoid errors, always explicitly define the window frame for LAST_VALUE() and NTH_VALUE(), ensure your PARTITION BY logic correctly groups data, and double-check that your ORDER BY clause reflects the intended sequence for "first" and "last."