LAST_VALUE Function [Aggregate]

Syntax

LAST_VALUE (expression [IGNORE NULLS | RESPECT NULLS])

OVER (window-spec)

Parameters

Returns

Data type of the argument.

Usage

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 expression, or if the ORDER BY expression is not precise enough to guarantee a unique ordering, then the result is arbitrary. If there is no window-spec, then the result is arbitrary.

You can specify elements of window-spec either in the function syntax (inline), or with a WINDOW clause in the SELECT statement.

Example

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:

Standards and Compatibility