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)