# `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.

## Syntax

```sql
first_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**:

```sql
SELECT <group_columns>,
       first_value(<value>) WITHIN GROUP (ORDER BY <sort_expression> [, ...])
FROM <table>
GROUP BY <group_columns>;
```

## Arguments

### Required

- `<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.

## Optional

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

## Returns

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`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/mode.md), [`PERCENTILE_CONT`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-cont.md), and [`PERCENTILE_DISC`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-disc.md). It's not used with an `OVER` clause.

## Considerations

- **Ordered-set aggregate:** `first_value` uses the same `WITHIN GROUP (ORDER BY …)` pattern as [`MODE`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/mode.md), [`PERCENTILE_CONT`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-cont.md), and [`PERCENTILE_DISC`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-disc.md). 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`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/min-by.md) and [`MAX_BY`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/max-by.md).
- **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](/docs/data/data-cloud-query-guide/references/dc-sql-reference/filter-clause.md), same as other aggregates.

## Examples

### First row in sort order for the whole table

Return the `product_name` from the row with the earliest `order_date`.

```sql
SELECT first_value(product_name) WITHIN GROUP (ORDER BY order_date ASC)
FROM orders;
```

### One value per group

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

```sql
SELECT account_id,
       first_value(status) WITHIN GROUP (ORDER BY updated_at ASC, id ASC)
FROM account_events
GROUP BY account_id;
```

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

### Descending order (pick “first” by highest metric)

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

```sql
SELECT team_id,
       first_value(player_name) WITHIN GROUP (ORDER BY score DESC, player_id ASC)
FROM team_scores
GROUP BY team_id;
```

## Related Documentation

- [MODE](/docs/data/data-cloud-query-guide/references/dc-sql-reference/mode.md)
- [PERCENTILE_CONT](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-cont.md)
- [PERCENTILE_DISC](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-disc.md)
- [MIN_BY](/docs/data/data-cloud-query-guide/references/dc-sql-reference/min-by.md) / [MAX_BY](/docs/data/data-cloud-query-guide/references/dc-sql-reference/max-by.md)
- [Aggregate Functions](/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.md)
- [GROUP BY clause](/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-group-by.md)