LAST_VALUE function [Aggregate]

Returns the last value from a set of values.

LAST_VALUE (expression [IGNORE NULLS | RESPECT NULLS])

OVER (window-spec)

LAST_VALUE returns the last value in a set of values, which is usually an ordered set. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS. If you specify IGNORE NULLS, then LAST_VALUE returns the last non-null value in the set, or NULL if all values are null.

The data type of the returned value is the same as that of the input value.

You cannot use LAST_VALUE or any other analytic function for expression. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expression.

If the window-spec does not contain an ORDER BY then the result is arbitrary. If there is no window-spec, then the result is arbitrary.

The following example returns the salary of each employee, plus the name of the employee with the highest salary in their department:

SELECT GivenName + ' ' + Surname AS employee_name, 
	Salary, DepartmentID,
	LAST_VALUE( employee_name ) OVER Salary_Window AS 	 highest_paid
FROM Employees
WINDOW Salary_Window AS ( PARTITION BY DepartmentID ORDER BY Salary 
	RANGE BETWEEN UNBOUNDED PRECEDING 
	AND UNBOUNDED FOLLOWING )
ORDER BY DepartmentID DESC;

The returned result set is: