Returns values from the last row of a window.
LAST_VALUE( [ ALL ] expression[ { RESPECT | IGNORE } NULLS ] ) OVER ( window-spec )
window-spec : see the Remarks section below
expression The expression to evaluate. For example, a column name.
Data type of the argument.
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 for the WINDOW clause. See WINDOW clause.
For more information about using window functions in SELECT statements, including working examples, see Window functions.
For more information about specifying a window specification in an OVER clause, see Window definition: Inlining using the OVER clause and WINDOW clause.
SQL/2008 Vendor extension.
SQL Anywhere supports SQL/2008 language feature F441, "Extended set function support", which permits operands of window functions to be arbitrary expressions that are not column references.
SQL Anywhere does not support optional SQL/2008 feature F442, "Mixed column references in set functions". SQL Anywhere does not permit the arguments of an aggregate function to include both a column reference from the query block containing the LAST_VALUE function, combined with an outer reference. For an example, see AVG function [Aggregate].
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.
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |