Скачать или смотреть 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.

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
   / @aarvikitchen5572  

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.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

Full SQL Server Course
   • SQL Server tutorial for beginners in arabic  

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
   / kudvenkatarabic