FIRST_VALUE and LAST_VALUE

What are FIRST_VALUE and LAST_VALUE?

FIRST_VALUE(column) returns the value from the first row in the window frame. LAST_VALUE(column) returns the value from the last row. These functions are useful for comparing each row against the first or last value in its group.

Syntax

SELECT column,
  FIRST_VALUE(column) OVER (
    PARTITION BY group_col ORDER BY sort_col
  ) AS first_val,
  LAST_VALUE(column) OVER (
    PARTITION BY group_col ORDER BY sort_col
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_val
FROM table_name;

When to Use

• Comparing each row to the first or last in its group
• Getting the earliest or latest value within a partition
• Building running comparisons against boundaries

Key Points

1. FIRST_VALUE — Returns the value from the first row of the window frame.
2. LAST_VALUE Gotcha — The default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE returns the current row's value. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
3. PARTITION BY — Defines the group. Without it, the entire result set is one partition.
4. ORDER BY — Determines which row is first and last.
5. NULL Handling — NULLs are included. Use IGNORE NULLS if available or filter them.