In SQL Server, the LAST_VALUE() function returns the last value in an ordered set of values.
LAST_VALUE() is a window function that enables us to get a value from the last row of a query result set or partition. This can be useful for when we want to do stuff such as compare a value from the current row with a value in the last row or include it in a calculation.
You may need to explicitly set the window frame if you want LAST_VALUE() to return the actual last value from the partition or result set. That’s because the default window frame ends with the current row. This is covered in the example below.
Example
Here a basic example to demonstrate:
SELECT
VendorId,
ProductName,
ProductPrice,
LAST_VALUE( ProductPrice ) OVER (
ORDER BY ProductPrice
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LAST_VALUE
FROM Products;Result:
VendorId ProductName ProductPrice LAST_VALUE -------- ------------------------------- ------------ ---------- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 245 1003 Hammock 10 245 1001 Long Weight (green) 11.99 245 1004 Tea Pot 12.45 245 1001 Long Weight (blue) 14.75 245 1001 Left handed screwdriver 25.99 245 1001 Right handed screwdriver 25.99 245 1002 Sledge Hammer 33.49 245 1003 Straw Dog Box 55.99 245 1003 Chainsaw 245 245
In this example I specified a window frame that included UNBOUNDED FOLLOWING. Specifically, I used a window frame of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This ensures that the window frame covers the whole partition/query result set.
The reason I did that is because the default window frame when using an ORDER BY clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The CURRENT ROW part means that LAST_VALUE() would simply pick its value from the current row (which means it would simply duplicate the ProductPrice column, which may have made the example slightly confusing).
Here’s what I mean:
SELECT
VendorId,
ProductName,
ProductPrice,
LAST_VALUE( ProductPrice ) OVER (
ORDER BY ProductPrice
) AS LAST_VALUE
FROM Products;Result:
VendorId ProductName ProductPrice LAST_VALUE -------- ------------------------------- ------------ ---------- 1004 Bottomless Coffee Mugs (4 Pack) 9.99 9.99 1003 Hammock 10 10 1001 Long Weight (green) 11.99 11.99 1004 Tea Pot 12.45 12.45 1001 Long Weight (blue) 14.75 14.75 1001 Left handed screwdriver 25.99 25.99 1001 Right handed screwdriver 25.99 25.99 1002 Sledge Hammer 33.49 33.49 1003 Straw Dog Box 55.99 55.99 1003 Chainsaw 245 245
So when we don’t explicitly specify a window frame, it uses a default frame that ends with the current row.
This only happens when we use an ORDER BY clause in a window function. You may assume that we could simply omit the ORDER BY clause, but in this case we can’t. The LAST_VALUE() function requires an ORDER BY clause in its OVER clause.
By the way, using a ROWS/RANGE clause also requires that the ORDER BY clause is specified.
Partitioning the Result Set
We can use a PARTITION BY clause with window functions to partition the result set by a given column. For example, we can partition by vendor:
SELECT
v.VendorName,
p.ProductName,
p.ProductPrice,
LAST_VALUE( ProductPrice ) OVER (
PARTITION BY p.VendorId
ORDER BY p.ProductPrice
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LAST_VALUE
FROM Products p
INNER JOIN Vendors v
ON v.VendorId = p.VendorId;Result:
VendorName ProductName ProductPrice LAST_VALUE ------------- ------------------------------- ------------ ---------- Mars Supplies Long Weight (green) 11.99 25.99 Mars Supplies Long Weight (blue) 14.75 25.99 Mars Supplies Left handed screwdriver 25.99 25.99 Mars Supplies Right handed screwdriver 25.99 25.99 Randy Roofers Sledge Hammer 33.49 33.49 Pedal Medals Hammock 10 245 Pedal Medals Straw Dog Box 55.99 245 Pedal Medals Chainsaw 245 245 Katty Kittens Bottomless Coffee Mugs (4 Pack) 9.99 12.45 Katty Kittens Tea Pot 12.45 12.45
Here we got the last value in each partition. Our LAST_VALUE column resets with each new partition.
In this case I used a SQL join to join the Products and Vendors table so that I could present the vendor names instead of just their IDs.
The OVER Clause is Required
We must provide the OVER clause when using window functions such as LAST_VALUE(). Here’s what happens if we remove it:
SELECT
VendorId,
ProductName,
ProductPrice,
LAST_VALUE( ProductPrice )
FROM Products;Result:
Msg 10753, Level 15, State 1, Line 5 The function 'LAST_VALUE' must have an OVER clause.
The ORDER BY Clause is Required
And our OVER clause must include an ORDER BY clause. Here’s what happens when we use an OVER clause without an ORDER BY clause:
SELECT
VendorId,
ProductName,
ProductPrice,
LAST_VALUE( ProductPrice ) OVER( )
FROM Products;Result:
Msg 4112, Level 15, State 1, Line 5 The function 'LAST_VALUE' must have an OVER clause with ORDER BY.
Documentation
For more information and examples, see Microsoft’s documentation for the LAST_VALUE() function.