Analytic Functions - Learning Module

Loading content...

0/241

FIRST_VALUE and LAST_VALUE

Accessing Boundary Values Within Windows

While LAG and LEAD access values at fixed offsets from the current row, many analytical questions require accessing values at the boundaries of a window frame: What was the first transaction of the day? What's the most recent salary for each employee? How does the current stock price compare to the opening price?

FIRST_VALUE and LAST_VALUE answer these questions directly. They retrieve the first or last value of an expression within the current window frame, enabling comparisons against reference points, baseline analysis, and aggregation-style lookups while preserving individual row context.

These functions are conceptually similar to aggregate functions like MIN and MAX, but with a crucial difference: they return specific row values (the first or last by some ordering) rather than mathematical extremes. The 'first' value might not be the minimum; it's simply the value that appears first in the ordered sequence.

Understanding FIRST_VALUE and LAST_VALUE requires understanding window frames—the subset of partition rows that each function considers. This interplay between function and frame is where the real power (and complexity) lies.

What You Will Learn

By the end of this page, you will fully understand FIRST_VALUE and LAST_VALUE syntax and execution semantics, master window frame specifications and their critical impact on results, handle NULL values with IGNORE NULLS and RESPECT NULLS, apply these functions to real-world scenarios from baseline comparisons to session analysis, and optimize performance for production workloads.

Conceptual Foundation

FIRST_VALUE and LAST_VALUE are positional window functions that extract values from specific positions within a window frame. Let's build a solid mental model before diving into syntax.

The Window Frame Model:

For each row being processed, the window frame defines which other rows are 'visible' to the window function. By default, the frame includes all rows from the partition start up to and including the current row—not the entire partition.

FIRST_VALUE: Returns the value of the expression evaluated at the first row of the current window frame.

LAST_VALUE: Returns the value of the expression evaluated at the last row of the current window frame.

Critical Insight: The window frame boundaries determine what 'first' and 'last' mean. With the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), LAST_VALUE always returns the current row's value—probably not what you expect!

Converting Mermaid diagram...

The LAST_VALUE Trap

This is the single most common mistake with LAST_VALUE. Due to default frame semantics, LAST_VALUE often returns the current row's value rather than the partition's last row. You almost always need to specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value.

Comparison with Related Functions:

Positional Functions Comparison
Function	Access Pattern	Relationship to Current Row	Frame-Dependent?
LAG	Fixed offset backward	Relative (n rows before)	No (ignores frame)
LEAD	Fixed offset forward	Relative (n rows after)	No (ignores frame)
FIRST_VALUE	First in frame	Absolute (frame start)	Yes (frame determines 'first')
LAST_VALUE	Last in frame	Absolute (frame end)	Yes (frame determines 'last')
NTH_VALUE	Nth position in frame	Absolute (position n)	Yes (frame determines scope)

Complete Syntax Reference

Let's examine the complete syntax for FIRST_VALUE and LAST_VALUE, including all optional clauses.

first_last_value_syntax.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- Full FIRST_VALUE syntax
FIRST_VALUE(expression [IGNORE NULLS | RESPECT NULLS])
OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST], ...
    [frame_clause]
)
 
-- Full LAST_VALUE syntax
LAST_VALUE(expression [IGNORE NULLS | RESPECT NULLS])
OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST], ...
    [frame_clause]
)
 
-- Frame clause options:
frame_clause ::=
    { ROWS | RANGE | GROUPS }
    BETWEEN frame_start AND frame_end
 
frame_bound ::=
    UNBOUNDED PRECEDING
  | offset PRECEDING
  | CURRENT ROW
  | offset FOLLOWING
  | UNBOUNDED FOLLOWING

FIRST_VALUE and LAST_VALUE Parameters
Parameter	Required	Default	Description
expression	Yes	—	Column or expression whose boundary value to retrieve
IGNORE/RESPECT NULLS	No	RESPECT NULLS	Whether to skip NULL values when finding first/last
PARTITION BY	No	Entire result set	Divides rows into independent groups
ORDER BY	Required*	—	Defines sequence within partition (*needed for meaningful results)
Frame clause	No	RANGE UNBOUNDED PRECEDING	Boundaries for 'first' and 'last' determination

basic_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Example table: daily_stock_prices
-- | trade_date | symbol | open_price | close_price |
-- |------------|--------|------------|-------------|
-- | 2024-01-02 | AAPL   | 185.00     | 186.50      |
-- | 2024-01-03 | AAPL   | 186.75     | 184.25      |
-- | 2024-01-04 | AAPL   | 184.00     | 187.00      |
-- | 2024-01-05 | AAPL   | 187.25     | 188.50      |
 
-- Get first close price (start of period) for comparison
SELECT 
    trade_date,
    symbol,
    close_price,
    FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol 
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_close_price,
    close_price - FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol 
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS change_from_start
FROM daily_stock_prices;
 
-- Result:
-- | trade_date | symbol | close_price | first_close_price | change_from_start |
-- |------------|--------|-------------|-------------------|-------------------|
-- | 2024-01-02 | AAPL   | 186.50      | 186.50            | 0.00              |
-- | 2024-01-03 | AAPL   | 184.25      | 186.50            | -2.25             |
-- | 2024-01-04 | AAPL   | 187.00      | 186.50            | 0.50              |
-- | 2024-01-05 | AAPL   | 188.50      | 186.50            | 2.00              |
 
-- Get last (most recent) value - NOTE the frame specification!
SELECT 
    trade_date,
    symbol,
    close_price,
    LAST_VALUE(close_price) OVER (
        PARTITION BY symbol 
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- Critical!
    ) AS latest_close_price
FROM daily_stock_prices;
 
-- Result: latest_close_price is 188.50 for ALL rows

Frame Specification Best Practice

For FIRST_VALUE, the default frame usually works (since 'first' from start-to-current equals first of entire partition). For LAST_VALUE, always explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to avoid the trap of getting the current row's value.

Understanding Window Frames in Depth

Window frames are central to FIRST_VALUE and LAST_VALUE behavior. Let's build deep understanding of frame types and their effects.

Frame Types:

ROWS: Physical row-based boundaries. ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING means exactly 3 rows before to 1 row after the current row.

RANGE: Logical value-based boundaries. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW includes all rows within a value difference of 7 from the current row's order column.

GROUPS: Group-based boundaries (PostgreSQL 11+, others). Groups rows with equal ORDER BY values into logical groups, then counts groups for frame bounds.

frame_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- ROWS vs RANGE demonstration
-- Table: daily_sales (date, revenue)
-- | date       | revenue |
-- |------------|---------|
-- | 2024-01-01 | 100     |
-- | 2024-01-02 | 150     |
-- | 2024-01-02 | 150     | <-- duplicate date!
-- | 2024-01-03 | 200     |
-- | 2024-01-04 | 175     |
 
-- With ROWS: Physical row boundaries (ignores duplicate dates)
SELECT 
    date,
    revenue,
    FIRST_VALUE(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS first_in_3_rows,
    LAST_VALUE(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS last_in_3_rows
FROM daily_sales;
 
-- With RANGE: Logical value boundaries (groups duplicate dates)
SELECT 
    date,
    revenue,
    FIRST_VALUE(revenue) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL '1 day' PRECEDING AND INTERVAL '1 day' FOLLOWING
    ) AS first_in_date_range,
    LAST_VALUE(revenue) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL '1 day' PRECEDING AND INTERVAL '1 day' FOLLOWING
    ) AS last_in_date_range
FROM daily_sales;

Frame Boundary Options:

Frame Boundary Specifications
Boundary	Meaning	Example Context
UNBOUNDED PRECEDING	Start of partition	All rows from beginning
n PRECEDING	n rows/values before current	Last 7 days
CURRENT ROW	Current row (ROWS) or value group (RANGE)	Up to/from now
n FOLLOWING	n rows/values after current	Next 3 rows
UNBOUNDED FOLLOWING	End of partition	All rows to end

frame_patterns.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Common frame patterns for FIRST_VALUE / LAST_VALUE
 
-- Entire partition (most common for LAST_VALUE)
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 
-- Rolling window: last 7 values
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
 
-- Forward-looking: current plus next 3
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING
 
-- Symmetric window: 5 rows centered on current
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
 
-- Date-based rolling (requires RANGE)
RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
 
-- Full history up to but not including current
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING

Default Frame Behavior

When ORDER BY is present: default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. When ORDER BY is absent: default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (entire partition). This difference often surprises developers.

NULL Handling with IGNORE/RESPECT NULLS

Real-world data often contains NULL values. FIRST_VALUE and LAST_VALUE offer explicit control over how NULLs are handled through the IGNORE NULLS and RESPECT NULLS clauses.

RESPECT NULLS (Default): If the first/last row in the frame has a NULL value for the expression, NULL is returned.

IGNORE NULLS: Skip NULL values when determining first/last, returning the first/last non-NULL value instead.

null_handling_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- Example: Sensor readings with gaps (NULLs)
-- | reading_time | sensor | value |
-- |--------------|--------|-------|
-- | 10:00        | A      | 100   |
-- | 10:01        | A      | NULL  |  <-- sensor offline
-- | 10:02        | A      | NULL  |  <-- still offline
-- | 10:03        | A      | 105   |
-- | 10:04        | A      | 108   |
 
-- RESPECT NULLS (default): Returns NULL if boundary value is NULL
SELECT 
    reading_time,
    sensor,
    value,
    FIRST_VALUE(value) OVER (
        PARTITION BY sensor 
        ORDER BY reading_time DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_value_respect_nulls  -- Would be 108 (first when DESC)
FROM sensor_readings;
 
-- IGNORE NULLS: Skip NULLs to find first non-NULL
SELECT 
    reading_time,
    sensor,
    value,
    FIRST_VALUE(value IGNORE NULLS) OVER (
        PARTITION BY sensor 
        ORDER BY reading_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_non_null_value,  -- 100 (skips subsequent NULLs)
    LAST_VALUE(value IGNORE NULLS) OVER (
        PARTITION BY sensor 
        ORDER BY reading_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_non_null_value    -- 108 (skips intervening NULLs)
FROM sensor_readings;

Use Cases for IGNORE NULLS:

When to Use IGNORE NULLS

•Last known good value: Sensors, IoT devices, or systems that occasionally fail to report. Use IGNORE NULLS to find the most recent actual reading.
•Optional fields with business meaning: When NULL means 'not applicable' rather than 'unknown', skip these when finding boundaries.
•Gap filling in sparse data: When data is recorded only on changes (event sourcing), find the last recorded state before current point.
•Data quality issues: Temporary NULL values from ETL failures that shouldn't affect analytics.

ignore_nulls_patterns.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- Pattern: Forward-fill NULLs with last known value
SELECT 
    event_time,
    customer_id,
    subscription_tier,
    FIRST_VALUE(subscription_tier IGNORE NULLS) OVER (
        PARTITION BY customer_id 
        ORDER BY event_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS effective_tier  -- Carries forward last known tier through NULLs
FROM customer_events;
 
-- Pattern: Find first and last actual transactions (skipping failed/null amounts)
SELECT 
    customer_id,
    FIRST_VALUE(transaction_date IGNORE NULLS) OVER (
        PARTITION BY customer_id 
        ORDER BY transaction_date
    ) AS first_successful_transaction,
    LAST_VALUE(amount IGNORE NULLS) OVER (
        PARTITION BY customer_id 
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS most_recent_valid_amount
FROM transactions
WHERE customer_id = 12345;
 
-- Pattern: Database-agnostic fallback (if IGNORE NULLS not supported)
-- Use subquery or COALESCE with LAG
WITH non_null_values AS (
    SELECT 
        reading_time,
        sensor,
        value,
        ROW_NUMBER() OVER (PARTITION BY sensor ORDER BY reading_time) AS rn
    FROM sensor_readings
    WHERE value IS NOT NULL
)
SELECT 
    s.reading_time,
    s.sensor,
    s.value,
    (SELECT value FROM non_null_values n 
     WHERE n.sensor = s.sensor AND n.rn = 1) AS first_non_null
FROM sensor_readings s;

Database Support Varies

IGNORE NULLS is part of the SQL standard but not universally implemented. Oracle, PostgreSQL 14+, and SQL Server 2022+ support it. For older systems, you may need workarounds using filtered subqueries or CTEs with ROW_NUMBER.

Real-World Use Cases

FIRST_VALUE and LAST_VALUE unlock powerful analytical patterns. Let's explore the most impactful real-world applications.

Comparing to a Fixed Reference Point:

Comparing current values to a baseline (first value of period, last known state, etc.) is foundational for financial and operational analytics.

baseline_comparison.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Financial: Compare stock price to period open
SELECT 
    trade_date,
    symbol,
    close_price,
    FIRST_VALUE(open_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('month', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS month_open,
    ROUND(100.0 * (close_price - FIRST_VALUE(open_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('month', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )) / FIRST_VALUE(open_price) OVER (
        PARTITION BY symbol, DATE_TRUNC('month', trade_date)
        ORDER BY trade_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ), 2) AS pct_change_from_month_open
FROM daily_stock_prices;
 
-- Operations: Compare current inventory to day-start level
SELECT 
    inventory_snapshot_time,
    product_id,
    quantity,
    FIRST_VALUE(quantity) OVER (
        PARTITION BY product_id, DATE(inventory_snapshot_time)
        ORDER BY inventory_snapshot_time
    ) AS day_start_quantity,
    quantity - FIRST_VALUE(quantity) OVER (
        PARTITION BY product_id, DATE(inventory_snapshot_time)
        ORDER BY inventory_snapshot_time
    ) AS quantity_change_today
FROM inventory_snapshots;

NTH_VALUE: The Generalization

NTH_VALUE generalizes FIRST_VALUE and LAST_VALUE, allowing you to access any positional value within the window frame—not just the first or last.

NTH_VALUE(expression, n) returns the value of the expression evaluated at the nth row of the window frame, where n is a positive integer.

Conceptually:

FIRST_VALUE(expr) ≈ NTH_VALUE(expr, 1)
There's no direct NTH_VALUE equivalent for LAST_VALUE (you'd need to know the frame size)

nth_value_syntax.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- NTH_VALUE syntax
NTH_VALUE(expression, n [FROM FIRST | FROM LAST] [IGNORE NULLS | RESPECT NULLS])
OVER (
    [PARTITION BY partition_expression, ...]
    ORDER BY sort_expression [ASC | DESC]
    [frame_clause]
)
 
-- Get second-place product in each category
SELECT 
    product_name,
    category,
    sales_amount,
    NTH_VALUE(product_name, 2) OVER (
        PARTITION BY category 
        ORDER BY sales_amount DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_place_product
FROM product_sales;
 
-- Get median value (middle of 5 in rolling window)
SELECT 
    date,
    value,
    NTH_VALUE(value, 3) OVER (
        ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING  -- 5-row window
    ) AS middle_value
FROM daily_values;
 
-- FROM LAST: Count from end of frame (PostgreSQL, Oracle)
SELECT 
    date,
    value,
    NTH_VALUE(value, 2 FROM LAST) OVER (
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_to_last_value
FROM daily_values;

Use Cases for NTH_VALUE:

When to Use NTH_VALUE

•Podium positions: Get not just the winner, but 2nd and 3rd place in competitions or rankings
•Statistical measures: Access median (middle) value in odd-sized windows without aggregation
•Skip patterns: Access every nth record in a sequence for sampling
•Relative positioning: 'Show me the 5th most recent event' type queries
•Data validation: Compare value at specific position against current value

NTH_VALUE Returns NULL If Position Doesn't Exist

If you request NTH_VALUE(expr, 5) but the frame only has 3 rows, NULL is returned. Unlike FIRST_VALUE (which always exists if the frame is non-empty), NTH_VALUE may return NULL for sparse frames.

Performance Considerations

FIRST_VALUE and LAST_VALUE are generally efficient, but understanding their performance characteristics ensures optimal query design.

Execution Characteristics:

1. Sorting Overhead: Like all window functions with ORDER BY, these require sorted input. Without a matching index, the database performs a full sort—O(n log n).

2. Frame Processing: The frame specification affects memory and computation:

UNBOUNDED frames may need to materialize entire partitions in memory
Bounded frames (ROWS n PRECEDING) can use sliding window algorithms with fixed memory
RANGE frames with non-constant bounds may be less optimizable

3. Multiple Window Functions: When multiple window functions share the same OVER clause, the database often computes them together in a single pass.

performance_patterns.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- Optimization: Consolidate window specifications
-- GOOD: Define window once for multiple functions
SELECT 
    date,
    value,
    FIRST_VALUE(value) OVER w AS first_val,
    LAST_VALUE(value) OVER w AS last_val,
    AVG(value) OVER w AS avg_val
FROM daily_values
WINDOW w AS (
    ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);
 
-- GOOD: Indexes that match window ORDER BY
CREATE INDEX idx_stock_symbol_date ON stock_prices(symbol, trade_date);
-- Now PARTITION BY symbol ORDER BY trade_date uses index efficiently
 
-- GOOD: Filter before windowing
WITH recent_data AS (
    SELECT * FROM stock_prices
    WHERE trade_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT 
    trade_date,
    symbol,
    FIRST_VALUE(close_price) OVER (
        PARTITION BY symbol ORDER BY trade_date
    ) AS month_first_close
FROM recent_data;
 
-- BE AWARE: UNBOUNDED FOLLOWING with large partitions
-- This may require buffering entire partition
SELECT 
    transaction_id,
    LAST_VALUE(amount) OVER (
        ORDER BY transaction_time
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- Needs full partition
    ) AS last_amount   -- Consider: do we really need the last value for each row?
FROM huge_transactions;
 
-- Alternative: Compute last value once and join
WITH summary AS (
    SELECT MAX(transaction_time) AS last_time
    FROM huge_transactions
)
SELECT t.*, (SELECT amount FROM huge_transactions WHERE transaction_time = s.last_time) AS last_amount
FROM huge_transactions t, summary s;

Frame Specification Performance Impact
Frame Type	Memory Usage	Optimization Potential	Notes
UNBOUNDED ... UNBOUNDED	High (full partition)	Limited	May materialize entire partition
UNBOUNDED PRECEDING only	Low (streaming)	Good	Can process without lookahead
Fixed ROWS (e.g., 7 PRECEDING)	Fixed (window size)	Excellent	Sliding window optimization
RANGE with expressions	Variable	Database-dependent	May require complex comparison

Question the Requirement

Before using LAST_VALUE with UNBOUNDED FOLLOWING on huge datasets, ask: 'Does every row really need the last value?' Often, you can compute boundary values once in a CTE or subquery and join, rather than computing per row.

Summary: FIRST_VALUE and LAST_VALUE

FIRST_VALUE and LAST_VALUE provide powerful access to boundary values within window frames, enabling sophisticated baseline comparisons and state lookups. Let's consolidate the essential knowledge.

Key Takeaways

•FIRST_VALUE and LAST_VALUE access frame boundaries: They return the first or last value within the current window frame, not fixed offsets like LAG/LEAD.
•Frame specification is critical: The default frame (UNBOUNDED PRECEDING to CURRENT ROW) makes LAST_VALUE return the current row's value—almost never what you want.
•Always specify full frame for LAST_VALUE: Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value.
•IGNORE NULLS skips NULL values: Essential for sparse data, sensor gaps, and event sourcing patterns where NULLs represent missing rather than meaningful values.
•NTH_VALUE generalizes to any position: Access the 2nd, 3rd, or nth value in the frame for ranking contexts and statistical measures.
•Baseline comparison is a primary use case: Compare current values to period starts (first value) or find most recent state (last value).
•Performance depends on frame size: UNBOUNDED frames may require materializing entire partitions; bounded frames enable streaming optimization.
•WINDOW clause consolidates specifications: Define complex windows once and reference multiple times for clarity and potential optimization.

What's Next:

Having mastered FIRST_VALUE and LAST_VALUE, we'll explore running totals—cumulative aggregations that maintain a running sum, count, or other aggregate as you move through ordered data. Running totals combine the power of aggregation with the row-preservation of window functions.

Page Complete

You now understand FIRST_VALUE and LAST_VALUE in depth—from frame semantics to NULL handling to performance optimization. These functions enable powerful baseline comparisons and boundary lookups that would otherwise require complex self-joins or subqueries. Practice with your own data to solidify these concepts.