SQL FIRST_VALUE Function

Discover the power of the FIRST_VALUE function, a window function that returns the first value in an ordered partition of the result set. This function is useful for anyone doing data analysis in SQL.

Window functions are a powerful category of functions that enable doing sophisticated data analysis in SQL. These functions operate over a "window" of rows related to the current row, allowing for preparing complex reports like running totals, moving averages, preparing rankings, and others. One of the common window functions is the FIRST_VALUE function.

The FIRST_VALUE function returns the first value in an ordered partition of a result set. The function can be helpful in scenarios where you compare a current value with the first value in a certain set:

• the change in stock prices from the first issue price to the current price, providing a view of a stock's performance since its debut.
• the change in product price from the first release price to the current price, to analyze price fluctuations or the discount level from the original price.
• the change in the first recorded vital signs (like blood pressure or glucose level) of a patient when first admitted to the hospital compared with later readings.
• compare the current temperature to the first temperature recorded on this date or in this year, for example to study climate changes or weather patterns.

The FIRST_VALUE function is the dual counterpart of another SQL window function, LAST_VALUE. FIRST_VALUE returns the first value in a data set, while LAST_VALUE returns the last value in a data set.

If you want to learn about window functions, I recommend our interactive Window Functions course. This course is great for anyone who wants to learn SQL window functions and use them to work with data more effectively. The course contains over 200 interactive exercises to help you gain confidence in your skills.

How to Use FIRST_VALUE Function

The FIRST_VALUE function returns the first value of an expression in a set ordered by another expression. The syntax for FIRST_VALUE is straightforward:

FIRST_VALUE(expression) OVER (
	[PARTITION BY column_1, column_2, ...]
	ORDER BY expression2
	[ROWS BETWEEN range_start AND range_end]
)

• expression – the column or expression from which you want to retrieve the first value.
• ORDER BY – defines the sorting of the data rows in each partition. FIRST_VALUE uses it to identify the first row from which to take the value.
• PARTITION BY – optional; divides the result set into partitions to which the FIRST_VALUE function is applied independently. If PARTITION BY is omitted, the function treats the whole result set as a single partition.
• ROWS BETWEEN – optional; defines the window frame, i.e. set of rows in each partition relative to the current row.

We demonstrate the FIRST_VALUE through examples using a stock_quotes table. The table contains stock prices for different companies (symbols) on different dates and times. The table has the following columns:

• id (integer) – A unique identifier for each record.
• symbol (text) – The stock symbol.
• quote_date (timestamp) – The date and time of the stock quote.
• price (decimal) – The price of the stock on that date.

Here are some sample rows from the table (with fictional data):

Example 1: Simple Use of FIRST_VALUE – the First Price for a Particular Company

This is the simplest usage of the FIRST_VALUE function: we want to find the first recorded price for a particular company, such as Apple Inc. (AAPL). We can use the following SQL query:

 SELECT
  quote_date,
  price,
  FIRST_VALUE(price) OVER (ORDER BY quote_date) AS first_price
 FROM stock_quotes
 WHERE symbol = 'AAPL';

This query only selects the stock prices for Apple Inc. (symbol AAPL). It selects the quote date and the price on this date. It uses the FIRST_VALUE function to select the first recorded price for the company. All stock prices for Apple are sorted with OVER (ORDER BY quote_date). The FIRST_VALUE function returns the price for the first row in this sorting, that is the first price ever recorded for Apple.

Example 2: FIRST_VALUE with PARTITION BY – First Price for All Symbols

You typically use FIRST_VALUE with PARTITION BY. This way the FIRST_VALUE function is applied separately in each partition defined by PARTITION BY. Suppose you need to find the first price for each company. Here’s the query you would write:

SELECT
  symbol,
  quote_date,
  price,
  FIRST_VALUE(price) OVER (PARTITION BY symbol ORDER BY quote_date) AS first_price
FROM stock_quotes;

In this query we partition the data by symbol (PARTITION BY symbol). This creates a separate partition for each company (symbol). The FIRST_VALUE function is applied to each partition separately. As in the previous example, we order the data by quote_date. The FIRST_VALUE function returns the price for the first row in each partition. Thus the query returns the symbol, the quote date, the price on this date, and the first recorded price for this symbol.

We can use the value of the FIRST_VALUE function to compute the percentage growth of the current price from the first recorded price, like this:

SELECT
  symbol,
  quote_date,
  price,
  FIRST_VALUE(price) OVER (
    PARTITION BY symbol 
    ORDER BY quote_date) AS first_price,
  100.0 * (price - FIRST_VALUE(price) OVER (
         PARTITION BY symbol 
         ORDER BY quote_date)) 
   / FIRST_VALUE(price) OVER (
         PARTITION BY symbol 
         ORDER BY quote_date) AS perc_growth
FROM stock_quotes;

Example 3. First Value for Each Day for Each Company

You can use the FIRST_VALUE function to compute the opening price (the first price of the day) for each date, like so:

SELECT DISTINCT
  symbol,
  CAST(quote_date AS DATE) AS date,
  FIRST_VALUE(price) OVER (
    PARTITION BY symbol, CAST(quote_date AS DATE) 
    ORDER BY quote_date) AS opening_price
FROM stock_quotes;

Here we partition the data by symbol (company) and the date. We cast the quote_date column to DATE to extract only the date value. As before, we use ORDER BY quote_date to sort the values by date. We use the FIRST_VALUE function and it selects the first row in each partition, that is the first price for each date for each company. This way we get the opening price for each day: the first price recorded on this date. We also use DISTINCT to get only one row for each date and symbol.

For more practical examples of SQL window functions, check out our article: SQL Window Functions Examples. You can also visit our article 11 SQL Window Functions Exercises with Solutions that contains practice exercises for window functions.

FIRST_VALUE and Other Window Functions

FIRST_VALUE is similar to other window functions.

FIRST_VALUE vs LAST_VALUE

The FIRST_VALUE function has its dual counterpart, LAST_VALUE. As the name suggests, the LAST_VALUE function returns the last value in the ordered partition of a result set. With FIRST_VALUE, you compare the current value with the starting point of the data, while with LAST_VALUE, you compare it with the ending point.

When you use the LAST_VALUE function, you have to remember to set the window frame to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to make sure it includes rows from the current row to the end of the partition. Otherwise OVER() uses the default window frame for ORDER BY and LAST_VALUE returns the value for the current row.

To remember this detail and other nuances of SQL window functions, we recommend our SQL Window Functions Cheat Sheet. It’s a downloadable quick reference guide for window functions.

MIN vs FIRST_VALUE

The FIRST_VALUE can easily be confused with the MIN function. The MIN function returns the smallest value in the dataset. The FIRST_VALUE function finds the value of one expression for the smallest row based on the expression given in ORDER BY.

You typically use the FIRST_VALUE function with two different expressions as the argument of the FIRST_VALUE function and in ORDER BY. This query, as we have seen many times in this article, returns the first recorded price of Apple, Inc.

SELECT
   quote_date,
   price,  
   FIRST_VALUE(price) OVER(ORDER BY quote_date) AS first_price
FROM stock_quotes
WHERE symbol = 'AAPL';

If you use the price column as both an argument of FIRST_VALUE and in ORDER BY, the query also returns the smallest price of Apple, Inc.

SELECT
   FIRST_VALUE(price) OVER(ORDER BY price) AS first_price
FROM stock_quotes
WHERE symbol = 'AAPL';

This query returns multiple rows, one for every row with Apple stock price.

This query returns the minimum price for Apple, Inc.:

SELECT
  MIN(price)
FROM stock_quotes
WHERE symbol = 'AAPL';

The query only returns one row with one value: the minimum price recorded for Apple.

You can use the MIN function as a window function:

SELECT
  quote_date,
  price,  
  MIN(price) OVER(ORDER BY quote_date) AS min_price
FROM stock_quotes
WHERE symbol = 'AAPL';

This query returns the running minimum of the price column ordered by quote_date. In other words, will return the smallest price encountered from the start of the dataset up to and including that row, when ordered by quote_date.

FIRST_VALUE vs. LEAD and LAG

FIRST_VALUE is also similar to functions LEAD and LAG. LEAD and LAG return the value in subsequent or preceding rows, while FIRST_VALUE returns the value of the first row in the partition. LEAD and LAG are best when you need to compare a sequence of values, for example you compare sales from one month to the next. FIRST_VALUE is used in scenarios where you reference the initial entry in a data set.

For an overview of the most important SQL window function topics, see our article Top 10 SQL Window Functions Interview Questions

Conclusion and Further Reading

The FIRST_VALUE function is an important SQL window function. It is useful for analyzing trends over time, such as seeing how stock prices or sales figures start out over a period. Learning how to use FIRST_VALUE can improve your ability to analyze time series data.

If you're interested in getting better at using SQL window functions, we recommend our interactive Window Functions course. It contains 218 hands-on exercises to help you learn window function through practice. If you already know window functions and are looking for more practice, check out our Window Functions Practice Set with 100 exercises to help you practice window functions on real-world scenarios.

For a quick guide on SQL window functions, check out the SQL Window Functions Cheat Sheet. It’s a quick reference guide for window functions that is available online and can be downloaded as PDF.

All our courses, including Window Functions and Window Functions Practice Set, are available as on their own or as part of our All Forever SQL Package. This package gives you lifetime access to all current and future SQL courses on our platform with a one-time payment. Whether you're starting out or advancing your SQL skills, this package supports your learning journey at every step. Sign up today to enhance your SQL expertise continuously.