# 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):

id | symbol | quote_date | price |
---|---|---|---|

1 | AAPL | 2023-04-01 9:00 | 150 |

2 | AAPL | 2023-04-02 9:05 | 148 |

3 | MSFT | 2023-04-01 9:00 | 250 |

4 | MSFT | 2023-04-02 9:05 | 255 |

5 | GOOGL | 2023-04-01 9:00 | 1200 |

6 | GOOGL | 2023-04-02 9:05 | 1210 |

### 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.