Last_Value Function return last value in an ordered set of values in SQL Server 2012. This function must have an OVER clause.
Syntax:
Last_Value(Column_Name) Over (Order By Column_Name ASC)
Example:
Create table #Temp ( ID int identity(1,1) , EmployeeName varchar(100), Age int ) Insert into #Temp(EmployeeName,Age) Select 'Hitesh',25 union Select 'Priyanka',23 union Select 'Dev',33 union Select 'Neha',25 union Select 'Abhimanyu',22 union Select 'Bhawesh',30
SQL Query using Last_Value Function
Select ID, EmployeeName, Age, Last_Value(Age) OVER (ORDER BY Age ASC) as Last from #Temp
Result:
Last
22
23
25
25
30
33
Last_Value changes for each record and is equal to the current value in the result set.
To get same Last Value for All Records
select ID, EmployeeName, Age, LAST_VALUE(Age) OVER (ORDER BY Age ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as Last from #Temp
This will return 33 as the last value.