LAST_VALUE returns the value from the last row within the specified window frame.
It is useful for retrieving ending values, although the window frame must be handled carefully.
Show each customer order together with the customer's latest order amount.
SELECT
c.CustomerName,
o.OrderDate,
o.OrderAmount,
LAST_VALUE(o.OrderAmount) OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate, o.OrderID
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS LatestOrderAmount
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID;Used in transaction history, latest-status analysis, customer lifecycle reporting, and financial analysis.
- LAST_VALUE depends on the window frame.
- UNBOUNDED FOLLOWING can ensure the actual final row is considered.
- ORDER BY defines the sequence.
- PARTITION BY defines the group.
- Careful frame definition is important.
Why can LAST_VALUE be confusing?
The default window frame in some systems may end at the current row, so the result may not represent the final row unless the frame is explicitly defined.
How can you retrieve the actual last value?
Define an appropriate window frame that extends through the final row of the partition.