> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference material for MAX_BY

# MAX_BY

Returns the value of the first argument for the row that contains the maximum of the second argument. If the maximum of the second argument is not unique, an arbitrary non-NULL value of the first argument is returned from the set of rows that maximize the second argument. If the first argument is NULL for all rows maximizing the second argument, NULL is returned.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
MAX_BY(<result>, <value>) [FILTER ([WHERE] <condition>)]
```

## Parameters

| Parameter     | Description                                                       | Supported input types |
| :------------ | :---------------------------------------------------------------- | :-------------------- |
| `<result>`    | The column from which the value is returned                       | Any type              |
| `<value>`     | The column that is maximized                                      | Any type              |
| `<condition>` | An optional boolean expression to filter rows used in aggregation | `BOOL`                |

## Return Types

Same as input type of `<result>`

## Examples

The `tournaments` table contains the following data:

<div className="query-window">
  ```
  SELECT name, totalprizedollars FROM tournaments ORDER BY totalprizedollars DESC LIMIT 5;
  ```

  | name <span>text null</span> | totalprizedollars <span>int null</span> |
  | :-------------------------- | :-------------------------------------- |
  | The Drifting Thunderdome    | 24768                                   |
  | The Talladega Thrill        | 24747                                   |
  | The Elite Speed Demons Cup  | 24346                                   |
  | The Volcanic Venture Rally  | 24323                                   |
  | The Drifting Wasteland      | 24271                                   |

  <p><span>Rows: 5</span><span>Execution time: 6.00ms</span></p>
</div>

The following example finds the tournament name with the highest prize amount in the `tournaments` table:

<div className="query-window">
  ```
  SELECT MAX_BY(name, totalprizedollars) AS maxprizetournament FROM tournaments;
  ```

  | maxprizetournament <span>text null</span> |
  | :---------------------------------------- |
  | The Drifting Thunderdome                  |

  <p><span>Rows: 1</span><span>Execution time: 6.87ms</span></p>
</div>

When multiple rows maximize the second argument, an arbitrary non-`NULL` value of the first argument is chosen. The result of the following query is non-deterministic — either `'d'` or `'e'` may be returned, as both maximize `value` while having a non-`NULL` key:

<div className="query-window">
  ```
  SELECT MAX_BY(key, value)
  FROM UNNEST(
      ARRAY['a', NULL, 'c', 'd', 'e', NULL],
      ARRAY[10,  100,   1,  100, 100, NULL]
  ) t(key, value);
  ```

  | max\_by <span>text null</span> |
  | :----------------------------- |
  | d                              |

  <p><span>Rows: 1</span><span>Execution time: 6.04ms</span></p>
</div>

If all rows maximizing the second argument have `NULL` in the first argument, `NULL` is returned:

<div className="query-window">
  ```
  SELECT MAX_BY(key, value) FROM UNNEST(
    ARRAY['a', NULL, 'c', 'd', NULL, 'f'],
    ARRAY[10, 100, 1, 2, 100, NULL]
  ) t(key, value);
  ```

  | max\_by <span>text null</span> |
  | :----------------------------- |
  | NULL                           |

  <p><span>Rows: 1</span><span>Execution time: 6.51ms</span></p>
</div>