LAST_VALUE

Purpose

For each row, this function returns the result of expr from the last 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