SQL FIRST_VALUE

What is SQL FIRST_VALUE?

FIRST_VALUE returns the first row's value in the window frame or partition, preserving the original data type.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FIRST_VALUE: PostgreSQL, MySQL 8.0+, MariaDB 10.2+, SQL Server 2012+, Oracle 12c+, Snowflake, BigQuery, Redshift, SQLite 3.25+, DuckDB

SQL FIRST_VALUE Full Explanation

FIRST_VALUE is an analytic (window) function that lets you look "forward" in your result set to retrieve the first value in the current window frame. Unlike aggregate functions, it does not collapse rows; it produces a value for every row while keeping row-level detail. The function operates after the WHERE, GROUP BY, and HAVING clauses but before the final ORDER BY, making it ideal for ranking, cohort analysis, and time-series calculations. Its behavior depends on the window frame definition: if you specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, it will always return the absolute first value in the partition; if you use a sliding frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, the result can change from row to row. NULLs are treated as ordinary values and returned as-is. If the frame contains no rows (rare but possible with RANGE frames), FIRST_VALUE returns NULL. Performance is generally on par with other window functions, but large frames or poorly indexed ORDER BY columns can increase sort cost.

SQL FIRST_VALUE Syntax

FIRST_VALUE(<value_expression>) OVER (
    [PARTITION BY <partition_expression>, ...]
    ORDER BY <order_expression> [ASC|DESC]
    [ROWS | RANGE BETWEEN frame_start AND frame_end]
);

SQL FIRST_VALUE Parameters

value_expression (Any) - Column or expression whose first value you want.
PARTITION BY (Column list) - Separates the data into independent partitions. Optional.
ORDER BY (Column list) - Defines row order inside each partition and thereby which row is first. Required.
ROWS (RANGE clause) - Frame spec|||Defines the subset of rows considered for each calculation. Optional but highly recommended.

Example Queries Using SQL FIRST_VALUE

-- Return each employee's first salary within their department
SELECT
  employee_id,
  department_id,
  salary,
  FIRST_VALUE(salary) OVER (
      PARTITION BY department_id
      ORDER BY hire_date ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_dept_salary
FROM employees;

-- Compare current daily sales to the first day of the month
SELECT
  order_date,
  daily_sales,
  FIRST_VALUE(daily_sales) OVER (
      PARTITION BY DATE_TRUNC('month', order_date)
      ORDER BY order_date
  )   AS first_day_sales,
  daily_sales - FIRST_VALUE(daily_sales) OVER (
      PARTITION BY DATE_TRUNC('month', order_date)
      ORDER BY order_date
  )   AS delta_from_day1
FROM sales_by_day;

Expected Output Using SQL FIRST_VALUE

Each query supplements every row with the first value in its partition
The first query shows the initial salary for each department next to every employee
The second query displays day-one sales for the month and the difference between current and day-one sales for every day in the month

Use Cases with SQL FIRST_VALUE

Show a starting balance, price, or metric for comparison across time.
Capture the earliest event detail (e.g., first login date) within user or cohort partitions.
Calculate deltas: current_value - FIRST_VALUE(initial_value).
Flag rows that match the first value, such as identifying a user's original plan tier.

Common Mistakes with SQL FIRST_VALUE

Forgetting ORDER BY, which is mandatory; without it, most dialects throw an error.
Omitting a frame clause when you actually need the entire partition. Default frames differ by database and may give unexpected rolling results.
Expecting FIRST_VALUE to ignore NULLs; it will return NULL if the first row's value is NULL.
Using FIRST_VALUE in GROUP BY queries when you really need MIN before aggregation.

First Introduced In

SQL:2003 standard

Frequently Asked Questions

What does SQL FIRST_VALUE return?

It copies the first value in the window frame to every row within that frame, allowing easy comparisons to a baseline.

Is ORDER BY mandatory in FIRST_VALUE?

Yes. ORDER BY defines which row is first. Without it, most systems raise an error or use an undefined default order.

How do I get the very first value in the whole partition?

Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to the window clause so the frame covers the entire partition.

What if the first value is NULL?

FIRST_VALUE will return NULL. If you want the first non-NULL value, combine it with IGNORE NULLS (Oracle) or use a subquery/conditional expression.