FIRST_VALUE

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the <value> from the first row in an ordered set. Within each GROUP BY group (or over the whole result if there is no GROUP BY), rows are sorted according to the WITHIN GROUP (ORDER BY …) clause. The function then returns the <value> from the row that comes first in that ordering.

1first_value(<value>) [IGNORE NULLS | RESPECT NULLS] WITHIN GROUP (ORDER BY <sort_expression> [, ...] [ASC | DESC] [NULLS { FIRST | LAST }] [, ...])

Use with GROUP BY when the ordering is applied per group:

1SELECT <group_columns>,
2       first_value(<value>) WITHIN GROUP (ORDER BY <sort_expression> [, ...])
3FROM <table>
4GROUP BY <group_columns>;

• <value>: Expression evaluated on the chosen row. Its result type is the return type of the aggregate.
• WITHIN GROUP (ORDER BY …): One or more sort expressions (with optional ASC / DESC and NULLS FIRST / NULLS LAST per key). Together they define which row is first in the ordered set for each aggregate evaluation.

• IGNORE NULLS: Skips rows containing NULL values. This is the default behavior.
• RESPECT NULLS: Considers rows containgin NULL values.

The type of <value>.

Null handling:

• If the aggregate has no input rows (empty group or table), the result is NULL.
• If the first row in the ordered set evaluates the <value> to NULL, the result is NULL.

Non-determinism: If multiple rows are tied at the first position (equal sort keys according to the ORDER BY), the engine returns the <value> from one of those tied rows. The function doesn’t guarantee which row it chooses unless your sort keys uniquely identify a row.

Ordered-set aggregate: first_value uses the same WITHIN GROUP (ORDER BY …) pattern as MODE, PERCENTILE_CONT, and PERCENTILE_DISC. It’s not used with an OVER clause.

• Ordered-set aggregate: first_value uses the same WITHIN GROUP (ORDER BY …) pattern as MODE, PERCENTILE_CONT, and PERCENTILE_DISC. It is not used with an OVER clause in this form.
• Grouping: Any non-aggregated columns in the SELECT list must appear in GROUP BY. The WITHIN GROUP ordering applies within each group.
• Alternative functions: To return a column from the row with the smallest or largest value in another column without WITHIN GROUP, see MIN_BY and MAX_BY.
• Null ordering: Ties and ordering of NULL sort keys follow your NULLS FIRST / NULLS LAST choices (and dialect defaults where omitted).
• Filtering: You can restrict which rows feed the aggregate using the FILTER clause, same as other aggregates.

Return the product_name from the row with the earliest order_date.

1SELECT first_value(product_name) WITHIN GROUP (ORDER BY order_date ASC)
2FROM orders;

Return the status from the earliest updated_at row in each account_id group.

1SELECT account_id,
2       first_value(status) WITHIN GROUP (ORDER BY updated_at ASC, id ASC)
3FROM account_events
4GROUP BY account_id;

Each output row has the status taken from the first row in that account’s ordered events.

Treat “first” as the row with the largest score by sorting descending.

1SELECT team_id,
2       first_value(player_name) WITHIN GROUP (ORDER BY score DESC, player_id ASC)
3FROM team_scores
4GROUP BY team_id;

• MODE
• PERCENTILE_CONT
• PERCENTILE_DISC
• MIN_BY / MAX_BY
• Aggregate Functions
• GROUP BY clause