SQL LAST_VALUE Function

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

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.

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 of the LAST_VALUE function.

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

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.

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

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

• Our Sql Window Functions Reference
• Our Sql FIRST_VALUE Function Reference
• Our Sql LAG Function Reference