LAST_VALUE function [Aggregate]

Returns values from the last row of a window.

Syntax
LAST_VALUE( expression[ { RESPECT | IGNORE } NULLS ] )
OVER ( window-spec )
window-spec : see the Remarks section below
Parameters
  • expression   The expression to evaluate. For example, a column name.

Returns

Data type of the argument.

Remarks

The LAST_VALUE function allows you to select the last value (according to some ordering) in a table, without having to use a self-join. This is valuable when you want to use the last value as the baseline in calculations.

The LAST_VALUE function takes the last record from the partition after doing the ORDER BY. Then, the expression is computed against the last record and results are returned.

If IGNORE NULLS is specified, the last non-NULL value of expression is returned. If RESPECT NULLS is specified (the default), the last value is returned whether or not it is NULL.

The LAST_VALUE function is different from most other aggregate functions in that it can only be used with a window specification.

Elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. See the window-spec definition provided in WINDOW clause.

For more information about using window functions in SELECT statements, including working examples, see Window functions.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

The following example returns the salary of each employee, plus the name of the employee with the highest salary in the same 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 );
employee_name Salary DepartmentID highest_paid
Michael Lynch 24903 500 Jose Martinez
Joseph Barker 27290 500 Jose Martinez
Sheila Romero 27500 500 Jose Martinez
Felicia Kuo 28200 500 Jose Martinez
Jeannette Bertrand 29800 500 Jose Martinez
Jane Braun 34300 500 Jose Martinez
Anthony Rebeiro 34576 500 Jose Martinez
Charles Crowley 41700 500 Jose Martinez
Jose Martinez 55500.8 500 Jose Martinez
Doug Charlton 28300 400 Scott Evans
Elizabeth Lambert 29384 400 Scott Evans
Joyce Butterfield 34011 400 Scott Evans
Robert Nielsen 34889 400 Scott Evans
Alex Ahmed 34992 400 Scott Evans
Ruth Wetherby 35745 400 Scott Evans
... ... ... ...

Jose Martinez makes the highest salary in department 500, and Scott Evans makes the highest salary in department 400.