intermediate

FIRST_VALUE Function in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

Overview

The FIRST_VALUE() function is an analytical window function in SQL that returns the value of a specified expression evaluated against the first row in an ordered window frame. It allows data engineers and analysts to compare each individual row in a partition against the initial or baseline event (such as comparing a user's current purchase to their initial registration date, or tracking baseline stock prices).

What You Will Learn

In this lesson, you will master:
  • Syntax & Semantics: Extracting baseline values with FIRST_VALUE(expression) OVER (...).
  • The Window Frame Default Trap: Why FIRST_VALUE() behaves predictably while LAST_VALUE() fails without custom framing.
  • Partitioning Applications: Comparing individual rows against group baselines (e.g., initial order amount per customer).
  • Ignoring NULL Values: Using RESPECT NULLS vs IGNORE NULLS across SQL dialects.
  • Comparison with FIRST_VALUE vs Subqueries: Writing clean $O(N \log N)$ analytical queries without self-joins.

Basic Syntax & Practical Use Case

Consider an e-commerce scenario where you want to display each customer's order history alongside the date of their very first order:

sql
SELECT 
    order_id,
    customer_id,
    order_date,
    order_amount,
    -- Extract the order date of the very first order placed by this customer
    FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS initial_order_date,
    
    -- Calculate days elapsed since the initial purchase
    order_date - FIRST_VALUE(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS days_since_first_order
FROM orders
ORDER BY customer_id, order_date;

Why FIRST_VALUE Works Easily While LAST_VALUE Often Confuses Developers

A common question in SQL interviews is why developers frequently encounter bugs when switching from FIRST_VALUE() to LAST_VALUE().

The Explanation:

By default, the window frame in an ordered window function is: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  • For FIRST_VALUE(): The first row in the window frame is UNBOUNDED PRECEDING (the absolute start of the partition). Because the start never moves, FIRST_VALUE() always returns the true first row!
  • For LAST_VALUE(): Under the default frame, the "last row" of the frame is CURRENT ROW. As a result, LAST_VALUE() simply returns the current row's own value on every step! To make LAST_VALUE() return the true final row of the partition, you must explicitly expand the frame to: ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING or ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Handling NULL Values: IGNORE NULLS

If the first row in an ordered partition happens to contain a NULL value, FIRST_VALUE() returns NULL by default (RESPECT NULLS).

In PostgreSQL 14+, Oracle, Snowflake, and BigQuery, you can instruct the engine to skip nulls:

sql
SELECT 
    patient_id,
    reading_timestamp,
    systolic_bp,
    -- Find the first valid, non-null blood pressure reading
    FIRST_VALUE(systolic_bp) IGNORE NULLS OVER (
        PARTITION BY patient_id
        ORDER BY reading_timestamp
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS baseline_valid_reading
FROM medical_vitals;

Comparative Analytics: Delta from First Value

A classic financial use case is calculating asset price return percentages relative to opening market value:

sql
SELECT 
    ticker,
    trade_timestamp,
    price,
    FIRST_VALUE(price) OVER (
        PARTITION BY ticker
        ORDER BY trade_timestamp
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS opening_price,
    -- Percentage change since opening
    ROUND(
        ((price - FIRST_VALUE(price) OVER (
            PARTITION BY ticker
            ORDER BY trade_timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )) / FIRST_VALUE(price) OVER (
            PARTITION BY ticker
            ORDER BY trade_timestamp
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )) * 100, 2
    ) AS pct_change_since_open
FROM stock_trades
WHERE trade_date = '2026-07-20';

Common Mistakes & Gotchas

  • Non-Deterministic Ordering: If the ORDER BY clause inside the window function contains duplicate values (e.g. multiple events occurring at the exact same millisecond timestamp), FIRST_VALUE() may pick an arbitrary row among ties. Always include a tie-breaker column (such as primary key ID: ORDER BY event_time, event_id).
  • Confusing FIRST_VALUE with MIN / MAX: MIN(val) finds the lowest numerical value across the partition. FIRST_VALUE(val) finds the value of val belonging to the row that ranks first according to the ORDER BY clause (which could be sorted on a completely different column like date).

Interview Perspective

SQL Interview Question: Why does LAST_VALUE() return the current row by default, while FIRST_VALUE() returns the partition's first row?

Key Talking Points for Candidates:

  1. Default Window Framing: The default framing for any analytical function with an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
  2. First Element: The beginning of the frame is anchored to the very beginning of the partition (UNBOUNDED PRECEDING), so FIRST_VALUE() correctly fetches the partition leader.
  3. Last Element: The end of the default frame is anchored to the CURRENT ROW, so LAST_VALUE() evaluates only up to the current row, returning itself. To fix LAST_VALUE(), one must explicitly specify BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Interactive Practice Challenge

Challenge: Calculate Customer Spend Growth

Write a query on the orders table returning customer_id, order_id, order_date, order_total, and a calculated column first_order_total showing each customer's initial order spend. Order results by customer and order date.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.