Free Tools

Resources

Blog

Docs

Pricing

Book a Demo

Get Free Extension

Get Sherloq Free

Back to Library

FIRST_VALUE() in SQL

Returns the first value in an ordered partition of a result set.

Syntax

FIRST_VALUE(expression) OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
    [frame_clause]
)

Return type

Same as input expression type

FIRST_VALUE() Function Example

SELECT product_name, sales_amount,
       FIRST_VALUE(sales_amount) OVER (ORDER BY sales_date) AS first_sale
FROM sales;
-- Output: 'Product A', 1000, 800 (800 being first sale)

What is FIRST_VALUE() in SQL?

The FIRST_VALUE() function in SQL returns the first value within an ordered partition of a result set. It is commonly used for retrieving the earliest record, identifying baseline values, and ranking datasets.

This function is supported in SQL Server, PostgreSQL, MySQL 8.0+, and Oracle as part of window functions.

This function is widely used in financial analysis, trend identification, and historical comparisons.

Parameters:

expression: Column or expression to retrieve
frame_clause (optional): Defines window frame boundaries

Example Use Cases:

-- Compare to best performing product

SELECT category, product_name, sales_amount,
       FIRST_VALUE(product_name) OVER (PARTITION BY category ORDER BY sales_amount DESC) 
       AS best_product
FROM product_sales;
-- Output:
category    | product_name    | sales_amount | best_product
------------|----------------|--------------|-------------
Electronics | iPhone 13      | 125000       | iPhone 13
Electronics | Galaxy S21     | 95000        | iPhone 13
Electronics | Pixel 6        | 75000        | iPhone 13
Clothing    | Winter Jacket  | 45000        | Winter Jacket
Clothing    | Jeans         | 35000        | Winter Jacket
Clothing    | T-Shirt       | 25000        | Winter Jacket
Books       | Harry Potter   | 15000        | Harry Potter
Books       | Lord of Rings | 12000        | Harry Potter

-- Track deviation from opening price

SELECT stock_symbol, trading_date, price,
       FIRST_VALUE(price) OVER (PARTITION BY stock_symbol ORDER BY trading_date) 
       AS opening_price
FROM stock_prices;
-- Output:
stock_symbol | trading_date | price  | opening_price
-------------|--------------|--------|---------------
AAPL         | 2024-01-01  | 185.50 | 185.50
AAPL         | 2024-01-02  | 187.25 | 185.50
AAPL         | 2024-01-03  | 186.75 | 185.50
GOOGL        | 2024-01-01  | 140.25 | 140.25
GOOGL        | 2024-01-02  | 142.50 | 140.25
GOOGL        | 2024-01-03  | 141.75 | 140.25
MSFT         | 2024-01-01  | 375.00 | 375.00
MSFT         | 2024-01-02  | 377.50 | 375.00
MSFT         | 2024-01-03  | 376.25 | 375.00

-- Initial score in competition

SELECT competitor_name, round_number, score,
       FIRST_VALUE(score) OVER (PARTITION BY competitor_name ORDER BY round_number) 
       AS initial_score
FROM competition_results;
-- Output:

competitor_name | round_number | score | initial_score
----------------|--------------|-------|---------------
John Smith      | 1           | 85    | 85
John Smith      | 2           | 88    | 85
John Smith      | 3           | 92    | 85
Maria Garcia   | 1           | 90    | 90
Maria Garcia   | 2           | 87    | 90
Maria Garcia   | 3           | 91    | 90
James Brown    | 1           | 82    | 82
James Brown    | 2           | 86    | 82
James Brown    | 3           | 89    | 82

Notes:

Behavior: Returns NULL if partition is empty
Performance Considerations: Window function optimization may be needed
Version Info: Core window function available in most modern DBMS
Deprecated/Recommended Alternatives: None, standard functionality

Error Handling:

Error: Syntax error if ORDER BY is omitted
Recommendation: Always specify ORDER BY and consider frame clause

Supported Databases:

DBMS

Function / Syntax

Example

Returns NULL

Related Functions:

LAST_VALUE()

Returns the last value in an ordered partition of a result set.

LAG()

Accesses data from a previous row in the result set within a specified window partition.

LEAD()

Accesses data from a subsequent row in the result set within a specified window partition.

ROW_NUMBER()

Assigns a unique sequential integer to rows within a result set partition.

CAST/CONVERT()

Converts a value from one data type to another. Both CAST and CONVERT perform the same function with different syntax.

COALESCE()

Returns the first non-null value in a list of expressions, evaluated from left to right.

FIRST_VALUE()

Returns the first value in an ordered partition of a result set.

DENSE_RANK()

Assigns a rank to rows within a result set partition, with no gaps in rank numbers when ties occur.

CASE

Provides conditional logic in SQL queries, evaluating conditions and returning specified results based on matching conditions.

RANK()

Assigns a rank to rows within a result set partition, with gaps in rank numbers when ties occur.

One place for all your queries,
directly on your SQL editor

Get Free Extension

Book a Demo

Get Sherloq Free

FIRST_VALUE() in SQL

Syntax

Return type

FIRST_VALUE() Function Example

What is FIRST_VALUE() in SQL?

Parameters:

Example Use Cases:

Notes:

Error Handling:

Supported Databases:

DBMS

Function / Syntax

Example

Behavior with NULL

MySQL

PostgreSQL

SQL Server

SQLite

BigQuery

Snowflake

Athena

Related Functions:

LAST_VALUE()

LAG()

LEAD()

ROW_NUMBER()

CAST/CONVERT()

COALESCE()

FIRST_VALUE()

DENSE_RANK()

CASE

RANK()

One place for all your queries, directly on your SQL editor

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

Explore

Resources

Free Tools

Legal

One place for all your queries,
directly on your SQL editor