LAST_VALUE function in SQL Server

Suggested Videos
Part 115 - FIRST_VALUE function in SQL Server
Part 116 - Window functions in SQL Server
Part 117 - Difference between rows and range



In this video we will discuss LAST_VALUE function in SQL Server.



LAST_VALUE function 

• Introduced in SQL Server 2012
• Retrieves the last value from the specified column
• ORDER BY clause is required
• PARTITION BY clause is optional
• ROWS or RANGE clause is optional, but for it to work correctly you may have to explicitly specify a value

Syntax : LAST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...)

LAST_VALUE function not working as expected : In the following example, LAST_VALUE function does not return the name of the highest paid employee. This is because we have not specified an explicit value for ROWS or RANGE clause. As a result it is using it's default value RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

SELECT Name, Gender, Salary,

    LAST_VALUE(Name) OVER (ORDER BY Salary) AS LastValue

FROM Employees



LAST_VALUE function working as expected : In the following example, LAST_VALUE function returns the name of the highest paid employee as expected. Notice we have set an explicit value for ROWS or RANGE clause to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

This tells the LAST_VALUE function that it's window starts at the first row and ends at the last row in the result set.

SELECT Name, Gender, Salary,

    LAST_VALUE(Name) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue

FROM Employees

LAST_VALUE function example with partitions : In the following example, LAST_VALUE function returns the name of the highest paid employee from the respective partition.

SELECT Name, Gender, Salary,

    LAST_VALUE(Name) OVER (PARTITION BY Gender ORDER BY Salary

    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue

FROM Employees