LAST_VALUE
Purpose
For each row, this function returns the result of expr from the first row in the window.
Syntax
last_value::=
Usage Notes
- Due to the fact that Exasol distributes all rows across the cluster, LAST_VALUE is non-deterministic as an aggregate function. Accordingly, LAST_VALUE serves primarily as a help function in the event where only the same elements are present in a group.
- LAST_VALUE as aggregate function ignores the RESPECT NULLS option.
- LAST_VALUE is also non-deterministic as analytic function if the over_clause does not contain an order_clause. For more information, refer to Analytic Functions section.
Example
Analytic Function
SELECT
id, department, hire_date,
LAST_VALUE(id) OVER (PARTITION BY department ORDER BY hire_date) LAST_
FROM employee_table ORDER BY department, hire_date;
Result
ID | DEPARTMENT | HIRE_DATE | LAST_ |
2005 | ACCOUNTS | 2013-01-01 | 2005 |
2003 | ACCOUNTS | 2015-07-01 | 2003 |
2002 | ACCOUNTS | 2017-01-01 | 2004 |
2004 | ACCOUNTS | 2017-01-01 | 2004 |
2001 | ACCOUNTS | 2018-07-01 | 2001 |
1003 | HR | 2014-01-01 | 1003 |
1002 | HR | 2016-01-01 | 1004 |
1004 | HR | 2016-01-01 | 1004 |
1001 | HR | 2018-01-01 | 1001 |