FIRST_VALUE
FIRST_VALUE returns the first value in a partition.
Syntax
FIRST_VALUE (expression) [ (RESPECT | IGNORE) NULLS] OVER (analytic_clause)
Remarks
• If the first value in the set is NULL, the function returns NULL unless you specify the optional IGNORE NULLS.
• IGNORE NULLS is useful for data densification.
Example
You want to find the most senior employee for each manager in an employee table. Use a query like the following:
FIRST_VALUE (name) OVER (PARTITION BY manager ORDER BY hire_date)
This query first partitions the employees by manager, then orders employees in each partition by hire date, and then applies the FIRST_VALUE function. However, because multiple employees might have been hired on the same date, repeated execution of this query could return a different ordering of same-day hires. To make sure the returned order is consistent, add a second expression to the ORDER BY clause:
FIRST_VALUE (name) OVER (PARTITION BY manager ORDER BY hire_date, ID)