FIRST_VALUE

Purpose

For each row, this function returns the result of expr from the first row in the window.

Usage Notes

• Due to the fact that Exasol distributes all rows across the cluster, FIRST_VALUE is non-deterministic as an aggregate function. Accordingly, FIRST_VALUE serves primarily as a help function in the event where only the same elements are present in a group.
• FIRST_VALUE as aggregate function ignores the RESPECT NULLS option.
• FIRST_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, 
    FIRST_VALUE(id) OVER (PARTITION BY department ORDER BY hire_date) FIRST_
FROM employee_table ORDER BY department, hire_date;

Result