LAST VALUE function in SQL Server

last_value function in sql server 2008
sql server last_value function returns incorrect data
sql server last_value function example
sql server last_value function with partition example

LAST_VALUE function in SQL Server

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

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/10/lastvalue-function-in-sql-server_9.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

Full SQL Server Course

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists