---
schema_version: "1"
id: 24d1da1190efb38ed2ba8a11e785a731b3fa2809e19a955e44d675888d888755
title: MAX_BY
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/aggregate-func/max-by.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/max-by.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 2bfaf1f498445f30409a197b788a1a2c204506a146313b3b33eee3f60c3e0477
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# `MAX_BY`

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

Returns the `<value>` from the row whose `<key>` is greatest among all rows in the aggregate (or within each group when used with `GROUP BY`).

## Syntax

```sql
max_by(<value>, <key>)
```

```sql
SELECT max_by(<value>, <key>) [IGNORE NULLS | RESPECT NULLS]
FROM <table>
GROUP BY <group_columns>;
```

## Arguments

### Required

- `<value>`: Any expression whose result is returned when its row wins the comparison. The aggregate result type matches this expression’s type.
- `<key>`: The expression `MAX-BY` uses to order the rows. The type must support comparison.

### Optional

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

## Returns

The `<value>` from the row with the maximum `<key>` among considered rows.

**Null handling:**

- You can combine `max_by` with other aggregates, `GROUP BY`, `HAVING`, subqueries, and expressions in the arguments (for example, `max_by(upper(name), score * 2)`).
- `max_by` only supports a single value to sort on. To sort using multiple sort criteria, use [FIRST_VALUE WITHIN GROUP](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/first-value-ordered-set.md).
- If the aggregate has no input rows (empty table or empty group), the result is `NULL`.
- If every row has a `NULL` key, those rows are skipped; if no row has a non-`NULL` key, the result is `NULL`.
- If the winning row’s `<value>` is `NULL`, the result is `NULL` (the key still determines which row wins).

**Tie-breaking:** If multiple rows share the same maximum key, one of the tied `<value>` results is returned; which tied row wins is implementation-dependent.

## Considerations

- `DISTINCT` is not supported (for example, `max_by(DISTINCT name, score)` is a syntax error).
- Rows with a `NULL` `<key>` are ignored when searching for the maximum key.
- You can combine `max_by` with other aggregates, `GROUP BY`, `HAVING`, subqueries, and expressions in the arguments (for example, `max_by(upper(name), score * 2)`).

## Examples

### Top scorer by score

Returns the name and score from the row with the highest `score`.

```sql
SELECT max_by(name, score) RESPECT NULLS, max(score) AS top_scorer
FROM (
  VALUES
    (1, 'Alice', 85),
    (2, 'Bob', 92),
    (3, 'Charlie', 78),
SELECT region, max_by(product, revenue), max(revenue) AS top_product
    (5, 'Eve', 88)
) AS t(id, name, score);
```

Returns `'David'`.

### Best product per region by revenue

Uses `GROUP BY` so each region gets its own arg-max product and returns the regions and their maximum revenue.

```sql
SELECT region, max_by(product, revenue), max(revenue) AS top_product
FROM (
  VALUES
    ('North', 'A', 100),
    ('North', 'B', 150),
    ('South', 'A', 200),
    ('South', 'B', 120)
) AS sales(region, product, revenue)
GROUP BY region;
```

Returns one row per region. For example `South` / `'A'` and `North` / `'B'`.

### NULL keys and NULL value for the winning row

Keys that are `NULL` are skipped. In this example, the largest non-`NULL` key is `15`, and that row’s `value` is `NULL`.

```sql
SELECT max_by(value, key_val) AS result
FROM (
  VALUES
    (10, 5),
    (20, NULL::int),
    (30, 10),
    (NULL::int, 15)
) AS t(value, key_val);
```

Returns `NULL`.

## Related Documentation

- [`MIN_BY`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/min-by.md)
- [`MAX`](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/max.md)
- [Aggregate Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.md)