LAST_VALUE (T-SQL)

Returns the last value in an ordered set of values in SQL Server 2012.
Syntax

LAST_VALUE ( [scalar_expression ) 
    OVER ( [ partition_by_clause ] order_by_clause rows_range_clause ) 

Examples

A.Using LAST_VALUE over partitions

The following example returns the hire date of the last employee in each department for the given salary (Rate). The PARTITION BY clause partitions the employees by department and the LAST_VALUE function is applied to each partition independently. The ORDER BY clause specified in the OVER clause determines the logical order in which the LAST_VALUE function is applied to the rows in each partition.

SELECT RepId
   ,ProductId
   ,ProductName
   ,ProductType
   ,Price
   ,LAST_VALUE (ProductName) OVER (PARTITION BY ProductType ORDER BY Price) AS TopProduct 
 FROM work.dbo.Product
 ORDER BY ProductType
Here is the result set.
B.Using FIRST_VALUE and LAST_VALUE in a computed expression

The following example uses the FIRST_VALUE and LAST_VALUE functions in computed expressions to show the difference between the sales quota value for the current quarter and the first and last quarter of the year respectively for a given number of employees. The FIRST_VALUE function returns the sales quota value for the first quarter of the year, and subtracts it from the sales quota value for the current quarter. It is returned in the derived column entitled DifferenceFromFirstQuarter. For the first quarter of a year, the value of the DifferenceFromFirstQuarter column is 0. The LAST_VALUE function returns the sales quota value for the last quarter of the year, and subtracts it from the sales quota value for the current quarter. It is returned in the derived column entitled DifferenceFromLastQuarter. For the last quarter of a year, the value of the DifferenceFromLastQuarter column is 0.

The clause “RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING” is required in this example for the non-zero values to be returned in the DifferenceFromLastQuarter column, as shown below. The default range is “RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”. In this example, using that default range (or not including a range, resulting in the default being used) would result in zeroes being returned in the DifferenceFromLastQuarter column.


Use SampleDB
SELECT BusinessEntityID
  ,DATEPART(QUARTER,QuotaDate) AS Quarter
  ,YEAR(quotadate) AS Salesyear
  ,SalesQuota AS QuotaThisQuarter
  ,SalesQuota - FIRST_VALUE(SalesQuota) OVER (PARTITION BY BusinessEntityId, YEAR(QuotaDate) 
  ORDER BY DATEPART(QUARTER,QuotaDate)) AS DifferenceFromFirstQuater
  ,SalesQuota - LAST_VALUE(SalesQuota) OVER (PARTITION BY  BusinessEntityId, YEAR(QuotaDate)
  ORDER BY DATEPART(QUARTER,QuotaDate)
  RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS DifferenceFromLastQuater
FROM sales.SalesPersonQuotaHistory
WHERE YEAR(Quotadate) > 2005
AND BusinessEntityID BETWEEN 274 AND 275 
ORDER BY BusinessEntityID, SalesYear, Quarter
Here is the result set.
BusinessEntityID Quarter     SalesYear   QuotaThisQuarter      DifferenceFromFirstQuarter DifferenceFromLastQuarter
---------------- ----------- ----------- --------------------- --------------------------- -----------------------
274              1           2006        91000.00              0.00                        -63000.00
274              2           2006        140000.00             49000.00                    -14000.00
274              3           2006        70000.00              -21000.00                   -84000.00
274              4           2006        154000.00             63000.00                    0.00
274              1           2007        107000.00             0.00                        -9000.00
274              2           2007        58000.00              -49000.00                   -58000.00
274              3           2007        263000.00             156000.00                   147000.00
274              4           2007        116000.00             9000.00                     0.00
274              1           2008        84000.00              0.00                        -103000.00
274              2           2008        187000.00             103000.00                   0.00
275              1           2006        502000.00             0.00                        -822000.00
275              2           2006        550000.00             48000.00                    -774000.00
275              3           2006        1429000.00            927000.00                   105000.00
275              4           2006        1324000.00            822000.00                   0.00
275              1           2007        729000.00             0.00                        -489000.00
275              2           2007        1194000.00            465000.00                   -24000.00
275              3           2007        1575000.00            846000.00                   357000.00
275              4           2007        1218000.00            489000.00                   0.00
275              1           2008        849000.00             0.00                        -20000.00
275              2           2008        869000.00             20000.00                    0.00