Using FIRST_VALUE and LAST_VALUE

Hello, welcome back!

In this post, I’m going to show you how to use FIRST_VALUE() and LAST_VALUE() window functions. These functions belong to the offset window functions, which are function that are able to take a peek at another row like LEAD() and LAG() as I showed you in one of my previous posts.

HOW THEY WORK?

If you remember LEAD() and LAG(), they work by accessing a row after or before the current row. In this case, FIRST_VALUE() and LAST_VALUE() can access the first or last row within the partition. This might seem like if you were using MIN() or MAX(), but they are different. The first value is probably not the minimum. These functions support framing, if you don’t remember what that is, I suggest you to check on this post where I talk about that.

So, in what cases do I need these functions? I remember working on a project where I had to fill several rows with the first value in the data set. Of course, there were several partition in my frame, it was something like this:

See that column called Value2, I needed to fill the nulls with the values from the first row of the “partition”. By the way, my data set was way way more complicated and ugly than this one, I just wanted to give you an example.

These two functions were introduced on SQL Server 2012, and I was working on 2005… 😓 I was able to achieve the same with a different approach using CTEs and MIN() and MAX() functions, but I’m sure my query could have been prettier and with better performance if I had this option.

GETTING THE FIRST VALUE OF THE PARTITION

Let’s create the table to show you how FIRST_VALUE() works.

USE TestDB
GO

-- Let's create a test table
CREATE TABLE dbo.ItemsFirstValue
(
    ID	INT IDENTITY(1,1) PRIMARY KEY
,   ItemID	VARCHAR(40)
,   Value1	INT NULL
,   Value2	INT NULL
)

-- And insert some data
INSERT INTO dbo.ItemsFirstValue
SELECT	'Item1',	12,	78	UNION ALL
SELECT	'Item1',	12,	NULL	UNION ALL
SELECT	'Item1',	12,	NULL	UNION ALL
SELECT	'Item2',	44,	46	UNION ALL
SELECT	'Item2',	44,	NULL	UNION ALL
SELECT	'Item2',	44,	NULL	UNION ALL
SELECT	'Item2',	44,	NULL	UNION ALL
SELECT	'Item2',	44,	NULL	UNION ALL
SELECT	'Item3',	53,	90	UNION ALL
SELECT	'Item3',	53,	NULL	UNION ALL
SELECT	'Item4',	75,	36	UNION ALL
SELECT	'Item4',	75,	NULL	UNION ALL
SELECT	'Item4',	75,	NULL

Now, let’s see and analyze the query.

SELECT
    ID, ItemID, Value1, value2
,   FIRST_VALUE(Value2) OVER(PARTITION BY ItemID ORDER BY ID ASC) AS FirstVal
FROM
    dbo.ItemsFirstValue

This is simple. We define a partition, in this case it’s the ItemID, we just grab that column because if you see, the ID of the table is incremental even though the ItemID is the same. Now, we have to give the function a value from the column we want, Value2. This is the value we’re going to pick. And don’t forget the ORDER BY is required. Here’s the results of the query:

See, it worked! It’s getting the first value of our partition. Now let’s see how we get the last value.

PULLING THE LAST VALUE

We just saw how to get the first value, now let’s see an example of how to get the last value. I’m going to use a table from Adventure Works because I’m lazy and that table is already created. If you don’t have a copy of the database, you can get it here.

Let’s say that, for some reason we need to query the table Sales.SalesOrderHeader and for every CustomerID and SalesOrderID, we need the last amount of the TotalDue column.

Now, let’s see the query and take a look at the results.

SELECT 
    CustomerID
,   SalesOrderID
,   TotalDue
,   LAST_VALUE(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID) AS AmountLastValue
FROM
    Sales.SalesOrderHeader
ORDER BY
    CustomerID, SalesOrderID;

The query is very simple, the syntax is exactly the same as in our example with FIRST_VALUE. Now, let’s see how the results look.

What the heck is going on there? Why we’re not getting the correct results if we wrote our query the same way? Well, I had already talked about this behavior in this post.

FRAMING!

Our results came back wrong, because we didn’t define a frame to the window function. The window frame is just like a delimiter defined for the partition. Think about framing something like a smaller window within the partition. The default frame, starts the the first row of the partition and includes all rows up to the current row, in our example above we didn’t tell SQL Server where to stop looking for values, so it took the current row as the last.

So, we need to use a frame that starts at the current row and includes all the rows to the end of the set. The one we need is ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. Let’s check it out.

SELECT 
    CustomerID
,   SalesOrderID
,   TotalDue
,   LAST_VALUE(TotalDue) OVER(PARTITION BY CustomerID ORDER BY SalesOrderID
                                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS AmountLastValue
FROM
    Sales.SalesOrderHeader
ORDER BY
    CustomerID, SalesOrderID;

And here’s the results.

See that!? It’s working! After we defined the right frame, SQL Server was able to get the data we needed.

That’s all for today’s blog post, I hope you guys enjoyed and had learned something new, or refreshed something you learned before. Code on my github repo as usual.

Don’t forget to like and share. Like my Facebook Page and, if you want to receive my blog posts and news regarding the website on your email, subscribe to my mailing list using the form below!

See you soon, cheers!

[mc4wp_form id=”331″]