FIRST_VALUE Function in SQL
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 whileLAST_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 NULLSvsIGNORE NULLSacross 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:
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 isUNBOUNDED 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 isCURRENT ROW. As a result,LAST_VALUE()simply returns the current row's own value on every step! To makeLAST_VALUE()return the true final row of the partition, you must explicitly expand the frame to:ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGorROWS 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:
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:
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 BYclause 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 ofvalbelonging to the row that ranks first according to theORDER BYclause (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:
- Default Window Framing: The default framing for any analytical function with an
ORDER BYisRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. - First Element: The beginning of the frame is anchored to the very beginning of the partition (
UNBOUNDED PRECEDING), soFIRST_VALUE()correctly fetches the partition leader. - Last Element: The end of the default frame is anchored to the
CURRENT ROW, soLAST_VALUE()evaluates only up to the current row, returning itself. To fixLAST_VALUE(), one must explicitly specifyBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Interactive Practice Challenge
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.