FIRST_VALUE

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns <value> evaluated at the row that’s the first row of the window frame.

1first_value(<value>) OVER (
2  [ PARTITION BY <expression> [, ...] ]
3  [ ORDER BY <expression> [, ...] ]
4  [ <frame_clause> ]
5)

• <value>: The expression to evaluate at the first row of the window frame.

Returns the same type as <value>.

• This is the window-function form of FIRST_VALUE, used with an OVER clause. For the ordered-set aggregate form used with WITHIN GROUP (ORDER BY ...), see FIRST_VALUE (ordered-set aggregate).
• first_value considers only the rows within the window frame, not the whole partition. Because the default frame always starts at UNBOUNDED PRECEDING (the first row of the partition), first_value returns the same, fixed value for every row in the partition under the default frame. Unlike last_value, it doesn’t need an explicit frame to produce a fixed result.
• If you narrow the frame’s start away from the beginning of the partition, for example ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, first_value evaluates to the current row’s own value instead, which changes as ORDER BY moves through the partition.
• For full details on PARTITION BY, ORDER BY, and frame specifications, see Window Function Call Syntax. To name a window definition for reuse, see the WINDOW clause in SELECT.

The default frame always starts at the first row of the partition, so first_value returns each department’s highest salary for every row in that department.

1SELECT depname, empno, salary,
2  first_value(salary) OVER (PARTITION BY depname ORDER BY salary DESC) AS highest_salary
3FROM empsalary;

Omitting PARTITION BY treats all rows as a single partition, so first_value returns the highest salary in the entire table for every row.

1SELECT empno, salary, first_value(salary) OVER (ORDER BY salary DESC) AS highest_salary
2FROM empsalary;

• LAST_VALUE
• NTH_VALUE
• FIRST_VALUE (ordered-set aggregate)
• Aggregate Functions
• Window Functions