Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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

sql server last_value returns incorrect data

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

sql server last_value function example

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

sql server last_value function with partition example

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.