FIRST_VALUE and LAST_VALUE: SQL window functions you should know

The SQL Server FIRST_VALUE and LAST_VALUE functions are simple window functions you should know to help you gather meaningful information from your data.

Window functions are considered an advanced tool when querying data in SQL Server. If you’re trying to learn about window functions, congratulations, you’re moving up!

The FIRST_VALUE and LAST_VALUE functions are two of four offset window functions we have available to us in Microsoft SQL Server. You should understand all the offset window functions if you want to be a great database developer. Check out the full tutorial on offset window functions to learn more:

SQL Server Offset Window Functions: FIRST_VALUE, LAST_VALUE, LAG, LEAD

In this very brief tutorial, we’re going to discuss what the FIRST_VALUE and LAST_VALUE functions are and how to use them.

Also, make sure you download your FREE Ebook:

FREE Ebook on SQL Server Window Functions!

This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. This Ebook will definitely be a great resource for you to reference throughout your career as a data professional. Get it today!

We’ll discuss these topics:

1. What is a window function?
2. What are the FIRST_VALUE and LAST_VALUE window functions?
3. Setting up some data
4. Example of using FIRST_VALUE
5. Example of using LAST_VALUE
6. Tips, tricks, and links

Let’s get into it.

1. What is a window function?

Before we dive into the FIRST_VALUE and LAST_VALUE functions, we need to understand what a window function is in the first place.

A window function can be used to combine aggregate/ranking/offset information AND detail information in a single query.

Window functions are excellent for writing a single query that shows ranking information AND detail information in a single query.

To understand FIRST_VALUE and LAST_VALUE, we need to understand terms like “window frame” and “window order clause“. If you don’t know what those are, you should definitely check out my complete introduction to window functions:

SQL Server Window Functions: An introduction for beginners

2. What are the FIRST_VALUE  and LAST_VALUE window functions?

The FIRST_VALUE and LAST_VALUE window functions will return an element from the first or last row in a window frame, respectively.

An element is simply a column value.

Here are some things to know about FIRST_VALUE and LAST_VALUE

• FIRST_VALUE and LAST_VALUE take one argument, which is the name of the column for which you want to see details (a.k.a. the element).
• They require the use of a window order clause. This is actually true for all offset window functions.
• They also require the use of a window frame clause. You can choose to leave out the frame, in which case SQL will use the frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW by default.

If you’re not sure what a “window frame” or a “window order clause” are, it’s because you didn’t read the introduction to window functions!

3. Setting up some data

To understand how the FIRST_VALUE and LAST_VALUE functions works, we need to set up some data. If you have been following along with any of the other window function tutorials, you will already have this data set up in your environment.

Run these SELECT statements to see if you already have the data:

SELECT * FROM Customers
SELECT * FROM Products
SELECT * FROM Orders

The data should look like this:

If you don’t have the data, let’s go ahead and add it. Here’s the Customers table:

--Create Customers table
CREATE TABLE Customers
(
CustID INT PRIMARY KEY IDENTITY(50,5),
FirstName VARCHAR(20),
LastName VARCHAR(20)
)

--Populate Customers it with data
INSERT INTO Customers(FirstName, LastName)
VALUES
('Joshua','Porter'),
('Andrew','Bluefield'),
('Jack','Donovan'),
('Cindy','Thatcher'),
('Gordon','Acres'),
('Gretchen','Hamilton')

The Products table:

--Create Products table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY IDENTITY(20,2),
ProductName VARCHAR(20),
Price DECIMAL(5,2)
)

--Populate Products table with data
INSERT INTO Products (ProductName, Price)
VALUES
('Large Bench',198.00),
('Small Bench',169.40),
('Coffee Table',220.00),
('Side Tables',265.20),
('Coat Rack',45.00)

Finally, the Orders table:

--Create Orders table
CREATE TABLE Orders
(
OrderID INT PRIMARY KEY IDENTITY(100,10),
CustID INT,
ProdID INT,
Qty TINYINT,
Orderdate DATETIME
)

--Populate Orders table with data
INSERT INTO Orders(CustID, ProdID, Qty, Orderdate)
VALUES
(55, 22, 1, '6/1/2021'),
(60, 28, 2, '6/6/2021'),
(75, 26, 1, '6/13/2021'),
(50, 20, 1, '7/1/2021'),
(55, 28, 1, '7/6/2021'),
(65, 24, 1, '7/14/2021'),
(55, 26, 1, '7/18/2021'),
(50, 26, 1, '7/24/2021'),
(70, 24, 1, '8/6/2021'),
(70, 26, 1, '8/6/2021'),
(70, 22, 3, '9/1/2021')

Notice the Orders table contains unofficial foreign key links to both the Customers and Products tables. I say they are “unofficial” because I didn’t actually create a foreign key constraint between the tables. It’s not necessary for this tutorial.

Ok, now that we have some data set up, we can show you examples of using FIRST_VALUE and LAST_VALUE.

4. Example of using FIRST_VALUE

Let’s run a quick query with a couple of JOINS to see all the data laid out nicely for us:

SELECT C.CustID, C.FirstName, C.LastName, P.ProductName, P.Price as 'Product Price', 
O.Qty, O.OrderDate
FROM Orders as O
INNER JOIN Customers AS C
ON O.CustID = C.CustID
INNER JOIN Products as P
ON O.ProdID = P.ProductID

This query will give us the following result:

Nice. In this result set, we can see some really great details about each order. We can see the following details:

• Who made the order
• What they bought
• How much that product costs
• How many of that product they bought
• The date they made the order

Let’s think about an example of where the FIRST_VALUE function might be useful. What if we want to know the very first product each customer purchased from us? In other words, we want to see what product originally peaked their interest and got them in the door. We might consider marketing those products better so that we get more people in the door.

As mentioned earlier, the FIRST_VALUE function takes one argument, which is the name of the column for which you want to see details (a.k.a. the element).

Here’s an example:

Understanding the parts of the FIRST_VALUE function

Let’s talk about what we have outlined in red in the query above:

1. The FIRST_VALUE function takes one argument: The name of the element you want to see. In our case, we want to see the name of the product the customer bought.
2. In the OVER clause, we have a PARTITION clause because we want to see a breakdown by customer (we want to partition the data by customer).
3. Also in the OVER clause, we have our window order clause, which in our case is “ORDER BY O.OrderDate“. If you think about it, the task of getting a first value doesn’t make sense if your data isn’t ordered in some way. Gnomesayin? This is why a window order clause is required when writing a query using FIRST_VALUE (or any offset window function).
4. Finally, we also have a window frame in the OVER clause. The FIRST_VALUE function requires that you use a window frame.

Understanding the window frame when using FIRST_VALUE

If you ask me, the only frame that makes sense is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(which can be written in a shorthand version:  ROWS UNBOUNDED PRECEDING ) . Remember, when working with a window function, SQL will process rows one at a time. When using FIRST_VALUE, you want to make sure when it’s processing a row, it considers everything before the current row. We do this by specifying UNBOUNDED PRECEDINGas the “preceding” delimiter. And we don’t care about anything after the current row, which is why we specify  CURRENT ROW as the “following” delimiter and not UNBOUNDED FOLLOWING. Why force SQL Server consider those rows when we know it won’t gather anything from them anyway?

Examining the results

So for our customer Joshua Porter, for example, it looks like the first product he purchased was the Large Bench. Of course, the product name is repeated for every row in the partition, but that’s ok.

For our customer Gordon Acres, it looks like the first product he purchased was the Coffee Table.

You get the idea.

I want to point out that we could have left out the PARTITION clause and the entire result set would have been treated as one large partition. In that case, our “FirstItemPurchased” column would show the first product ever purchased. Period:

(I’ve also demonstrated how you can leave off the window frame and SQL defaults to using the frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

As you can see, the first value of “Small Bench” is repeated for all rows in the partition, which is ok!

5. Example of using LAST_VALUE

Folks, LAST_VALUE is the same idea, except is returns details about the last row in the partition.

You need to think about the window frame you want to use with LAST_VALUE. You basically want to make sure the ending delimiter is UNBOUNDED FOLLOWING. Remember, when working with a window function, SQL will process rows one at a time. You want to make sure when it’s on a row, it considers everything after the current row too. We do this by specifying UNBOUNDED FOLLOWING.

Here’s an example:

Notice the order of the OrderDates. Now, they are in descending order. In other words, in each partition, it’s showing the last (a.k.a. most recent) item purchased at the top.

Again, notice the frame. I could have used ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and gotten the same results, but when it comes to LAST_VALUE, we don’t really care about anything before the current row, right? So why make SQL Server consider those rows when we know it won’t gather anything from them anyway?

Gnomesayin?

6. Tips, tricks, and Links

Here is a list of a few tips and tricks you should know when working with the FIRST_VALUE and LAST_VALUE window functions:

Tip # 1: FIRST_VALUE and LAST_VALUE were introduced in SQL Server 2012

If you try to use FIRST_VALUE or LAST_VALUE in a SQL Server version that is older than 2012, it won’t work. These functions were introduced with SQL Server 2012, so they will only work with that version or higher.

There is, however, a roundabout way of getting a first value without using the FIRST_VALUE function. It involves creating a table expression (like a CTE) that uses the ROW_NUMBER window function, then filtering the results of that table expression to only display rows where the value in the ROW_NUMBER column is 1.

Here is a great Stack Overflow article that talks about how to do it: Alternate to FIRST_VALUE in SQL Server 2008.

Tip # 2: You should always specify a window frame

Earlier, I demonstrated the use of FIRST_VALUE without a window frame. I discussed how when the frame is omitted from your OVER clause, SQL will use the frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW by default. The thing about the RANGE window unit is that it’s not as efficient as the ROWS window unit. Microsoft makes that very clear. So do yourself a favor and just always specify your own frame!

If you’re not sure what the difference is between ROWS and RANGE, check out this tutorial:

What’s the difference between ROWS and RANGE? Explained!

Tip # 3: FIRST_VALUE and LAST_VALUE can only be used in the SELECT list or ORDER BY clause.

You can only use FIRST_VALUE or LAST_VALUE in the SELECT list or an ORDER BY clause. This rule is actually true for all window functions.

Links:

Everything discussed in this tutorial can be found in the following FREE Ebook:

Download your FREE SQL Server Window Functions EBook!

This FREE EBook discusses absolutely everything you need to know about window functions in SQL Server, including everything discussed in this tutorial. A proper understanding of window functions is essential for anyone looking to enter the field of data science. This Ebook will definitely be a great resource for you to reference throughout your career. Get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

The FIRST_VALUE function is one of four offset window functions available to us in Microsoft SQL Server. You need to understand all the different offset functions if you want to be the world’s best database developer. Take a look at the full tutorial on all the offset functions here:

SQL Server Offset Window Functions: FIRST_VALUE, LAST_VALUE, LAG, LEAD

Also, don’t forget to check out the four ranking window functions. It’s definitely important to know them, too:

SQL Server Ranking Window Functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE

Also, if you missed the introduction to window functions, it discusses the different aggregate window functions you can use in SQL Server. That tutorial is a MUST-READ, because it discusses all the parts involved in a window function that you need to know in order to write a window function query correctly. Don’t skip that tutorial, you can find it here:

SQL Server Window Functions: An introduction for beginners

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!