SQL LAST_VALUE Function

The LAST_VALUE function returns the last value in a result set.

Multiple groups can be created by partioning the result set.

In that case, LAST_VALUE returns the last value inside each group.

Note: Alternatively, use FIRST_VALUE and sort in the opposite direction. The results are the same.

Example

#

List products with pricing. In each row, add lowest and highest prices.

SELECT ProductName, 
       LAST_VALUE(UnitPrice) OVER (ORDER BY UnitPrice DESC
          ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)  
          AS Low,
       UnitPrice AS Price,   
       LAST_VALUE(UnitPrice) OVER (ORDER BY UnitPrice
          ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
          AS High
  FROM Product
 ORDER BY UnitPrice DESC

Unit prices appear between the lowest and the highest prices.

Result:  78 records
ProductName Low Price High
Côte de Blaye 2.50 263.50 263.50
Thüringer Rostbratwurst 2.50 123.79 263.50
Mishi Kobe Niku 2.50 97.00 263.50
Sir Rodney's Marmalade 2.50 81.00 263.50
Carnarvon Tigers 2.50 62.50 263.50

Syntax

#

Syntax of the LAST_VALUE function.

LAST_VALUE(scalar-expression) 
  OVER( PARTITION BY expression, ...
        ORDER BY expression, ...
        [ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING] )

scalar-expression -- a column, subquery, or expression evaluated into single value.

PARTITION BY expression, ... -- optional. Organizes rows into different groups where the LAST_VALUE will be applied. If not specified, the entire result set represents a single group.

ORDER BY expression -- sets the sort order of the result set.

[ROWS BETWEEN ... -- optional. See description below.

You may find that LAST_VALUE does not return the expected results. Adding the ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING clause can fix this.

Below is an explanation from Stackoverflow on why this clause is needed.

To see what is happening, you must understand the concept of framing. The frame allows you to specify a set of rows for the window that is even smaller than the partition. The default frame contains the rows starting with the first row and up to the current row. For row 1, the window is just row 1. For row 3, the window contains rows 1 to 3. When using FIRST_VALUE, the first row is included by default, so you don’t have to worry about it to get the expected results.

When using LAST_VALUE with the default frame, the window only goes up to the current row. The current row is the last row of the window. To get around this problem, you must specify the frame, in this case ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. This means that the window starts with the current row and ends at the last row of the partition. Run the next example to see how to use LAST_VALUE correctly.

Tip: To circumvent this issue use FIRST_VALUE with the opposite sort order. This will return the results you would expect from LAST_VALUE.

More Examples

LAST_VALUE with PARTITIOIN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List the orders for the year 2013. Include the lowest and highest sales amount for each month.
SELECT MONTH(OrderDate) AS Month,
       CONCAT(FirstName, ' ', LastName) AS Customer, 
       LAST_VALUE(TotalAmount) OVER(PARTITION BY MONTH(OrderDate)
                        ORDER BY TotalAmount DESC 
                        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
                        AS 'Lowest Monthly Sale',
       TotalAmount, 
       LAST_VALUE(TotalAmount) OVER(PARTITION BY MONTH(OrderDate)
                        ORDER BY TotalAmount 
                        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
                        AS 'Highest Monthly Sale'
  FROM [Order] O
  JOIN Customer C ON O.CustomerId = C.Id
 WHERE YEAR(OrderDate) = 2013
 ORDER BY MONTH(OrderDate), TotalAmount DESC
Result:  408 records
Month Customer Lowest Monthly Sale TotalAmount Highest Monthly Sale
1 Jean Fresnière 49.80 11493.20 11493.20
1 Jytte Petersen 49.80 11283.20 11493.20
1 Roland Mendel 49.80 5796.00 11493.20
2, 3, 4, ... 11
12 Pascale Cartrain 12.50 28.00 6984.50
12 Paolo Accorti 12.50 18.40 6984.50
12 Patricio Simpson 12.50 12.50 6984.50

You may also like



Guides


vsn 3.1