# SQL MAX Function: Syntax and Examples

The max SQL function returns the largest value in a set of rows. You can use it on a whole table, on each group created by `GROUP BY`, or as a window function that keeps every row. This article covers the syntax, how the function evaluates values, and worked queries you can run yourself.

## Quick Answer

- `MAX(expression)` returns the highest value in a column across the rows the query selects.
- It works with numeric, character, `uniqueidentifier`, and datetime columns, but not with `bit` columns [1].
- `MAX` ignores NULL values. If no row qualifies, it returns NULL [1].
- Combine it with `GROUP BY` to get one maximum per group, such as the highest sale per region.
- For character columns, the maximum is the highest value in the collating sequence, not the longest string [1].

## Syntax

The basic form is:

```sql
MAX ( [ ALL | DISTINCT ] expression )
```

The `OVER` clause turns it into a window function:

```sql
MAX ( [ ALL | DISTINCT ] expression )
    OVER ( [ partition_by_clause ] [ order_by_clause ] )
```

| Argument | Required? | Meaning |
|---|---|---|
| `expression` | Yes | A constant, column name, or function, plus any combination of arithmetic, bitwise, and string operators. Aggregate functions and subqueries are not permitted [1]. |
| `ALL` | No | Applies the function to every value. This is the default. |
| `DISTINCT` | No | Removes duplicate values before the function runs. It does not change the result of `MAX`, since the largest value is the same either way [2]. |
| `OVER` | No | Turns `MAX` into a window function. Without it, `MAX` collapses rows into a single result row. |
| `partition_by_clause` | No | Divides the result set into partitions. If you omit it, the function treats all rows as one group [1]. |
| `order_by_clause` | No | Sets the logical order in which the operation is performed [1]. |

## How It Works

`MAX` is an aggregate function. It operates on a set of rows and returns one value for that set [1]. The engine reads the rows produced by the `FROM` and `WHERE` clauses, discards NULLs in the target expression, and compares the remaining values.

For numbers, the comparison is numeric. For dates, the latest date wins. For text, the comparison follows the column's collating sequence, so `MAX` on a `varchar` column returns the value that sorts last under that collation [1]. That is why `MAX` on a name column can return something like `'Sofia'` even when a longer name exists.

Two behaviors matter in practice. First, `MAX` returns NULL when there is no row to select, so an empty group or a `WHERE` clause that matches nothing gives you NULL instead of zero [1]. Second, `MAX` is deterministic when used without the `OVER` and `ORDER BY` clauses, and nondeterministic when you specify both [1].

When you add `OVER`, the function no longer collapses rows. It computes the maximum over a window and repeats that value on each row in the window. This is how you compare each row against the best value in its group without losing detail.

## Worked Example

The `sales` table below holds ten sales records across four regions. Each row has an `id`, a `region`, and an `amount`.

| id | region | amount |
|---|---|---|
| 1 | North | 1200.50 |
| 2 | North | 980.00 |
| 3 | North | 1450.75 |
| 4 | South | 2100.00 |
| 5 | South | 1750.25 |
| 6 | East | 890.00 |
| 7 | East | 1320.40 |
| 8 | East | 1105.60 |
| 9 | West | 2400.00 |
| 10 | West | 1980.90 |

To find the highest sale in each region, group the rows by region and apply `MAX` to the amount:

```sql
SELECT region, MAX(amount) AS highest_sale FROM sales GROUP BY region;
```

The query runs in four steps:

1. `FROM sales` selects the sales table as the source of rows.
2. `GROUP BY region` collapses rows into one group per distinct region.
3. `MAX(amount)` returns the largest amount within each region group.
4. `SELECT region, MAX(amount)` outputs the region alongside its highest sale.

The result has one row per region:

| region | highest_sale |
|---|---|
| East | 1320.40 |
| North | 1450.75 |
| South | 2100.00 |
| West | 2400.00 |

The result was checked with an equivalent SQLite query. Notice that West has the highest single sale at 2400.00, while East's best is 1320.40. The output is sorted by region because SQLite returned the groups in that order, and you should add an explicit `ORDER BY` when the row order matters to you.

## More Examples

**Highest value in the whole table.** Drop the grouping to get a single number:

```sql
SELECT MAX(amount) AS top_sale FROM sales;
```

This returns 2400.00, the largest amount anywhere in the table.

**Maximum with a filter.** Add a `WHERE` clause to restrict the rows before aggregation:

```sql
SELECT MAX(amount) AS top_east_sale FROM sales WHERE region = 'East';
```

This returns 1320.40. The filter runs before `MAX`, so only East rows are considered.

**Maximum per group with a row count.** Aggregate functions combine well. Pair `MAX` with SQL COUNT to see how many rows produced each maximum:

```sql
SELECT region, COUNT(*) AS sales_count, MAX(amount) AS highest_sale
FROM sales
GROUP BY region
ORDER BY highest_sale DESC;
```

**Maximum as a window function.** Keep every row and add the group maximum beside it:

```sql
SELECT id, region, amount,
       MAX(amount) OVER (PARTITION BY region) AS region_best
FROM sales;
```

Each row now carries the best amount for its region. You can subtract `amount` from `region_best` to see how far each sale sits below the regional top. Window functions like this pair naturally with ranking functions such as SQL RANK and SQL ROW_NUMBER when you need to pick the top row per group.

**Maximum on text and dates.** The same function works on other comparable types:

```sql
SELECT MAX(region) AS last_region FROM sales;
```

For text, this returns the value that sorts last under the column's collation [1]. On a date column, `MAX` returns the most recent date.

## Errors and How to Fix Them

**Column not contained in either an aggregate function or the GROUP BY clause.** This happens when you select a column that is neither grouped nor aggregated. Fix it by adding the column to `GROUP BY` or wrapping it in an aggregate.

**Cannot perform an aggregate function on an expression containing an aggregate or a subquery.** You cannot nest `MAX` inside another aggregate in the same expression [1]. Compute the inner value in a subquery or a common table expression, then aggregate the result.

**Operand data type bit is invalid for max operator.** `MAX` does not accept `bit` columns [1]. Cast the column to an integer type first.

**Unexpected NULL result.** `MAX` returns NULL when no row qualifies [1]. Check your `WHERE` clause and confirm the table actually holds rows for the filter you applied.

**Wrong maximum on text.** If `MAX` on a text column returns a value you did not expect, the collation is deciding the order. Compare the collation of the column against the order you assumed.

## Common Mistakes

- **Assuming `MAX` returns the row, not the value.** `MAX(amount)` gives you a number, not the full record. To get the whole row, filter with a subquery or use a window function and keep the top row.
- **Forgetting `GROUP BY`.** Selecting `region` and `MAX(amount)` without `GROUP BY` raises an error in most databases. Add the grouping column.
- **Expecting `MAX` to count NULLs as values.** NULLs are eliminated before the comparison, so a column full of NULLs returns NULL [1].
- **Using `MAX` to find the longest string.** `MAX` compares sort order, not length [1]. Use SQL LENGTH with `ORDER BY` when length is what you want.
- **Assuming `DISTINCT` changes the answer.** `MAX(DISTINCT amount)` and `MAX(amount)` return the same value because duplicates never affect the largest element [2].
- **Relying on implicit row order.** Aggregate queries do not guarantee output order. Add an explicit `ORDER BY` when the sequence matters.

## Limitations

`MAX` returns a single scalar per group, so it cannot tell you which row produced that value or how many rows tie for the top spot. If two regions share the same highest amount, `MAX` still returns one number per group and gives you no signal that a tie exists. You need a separate query or a window function to surface the tied rows.

The function also depends entirely on the data type and collation of the column you pass in. On text columns, the "maximum" is a sort-order artifact, which can mislead anyone who reads it as a measure of size or importance. On nullable columns, a group where every value is NULL returns NULL, which is easy to mistake for a missing group. Finally, `MAX` cannot be nested inside another aggregate in the same expression, so multi-level questions need subqueries or common table expressions [1].

## Frequently Asked Questions

### What does the max SQL function do?

`MAX` returns the largest value in a set of rows for the expression you give it. Used alone, it scans the whole result set. Used with `GROUP BY`, it returns one maximum per group. Used with `OVER`, it returns the maximum for a window while keeping every row.

### Does MAX ignore NULL values?

Yes. NULLs are eliminated before the comparison runs, so they never win and never affect the result [1]. If every value in the group is NULL, or if no row qualifies, `MAX` returns NULL [1].

### Can I use MAX on text columns?

Yes. `MAX` accepts `char`, `nchar`, `varchar`, and `nvarchar` columns [1]. The result is the value that sorts last in the column's collating sequence, which is not the same as the longest string [1].

### How do I get the full row with the maximum value?

Wrap the aggregate in a subquery and filter the outer query against it, or use a window function such as `MAX(amount) OVER (PARTITION BY region)` and keep the rows where the amount equals the window maximum. Ranking functions like SQL ROW_NUMBER also work well when you need exactly one row per group.

### What is the difference between MAX and GREATEST?

`MAX` operates on a set of rows and returns one value for that set [1]. `GREATEST` compares values inline within a single row, so it returns the largest of the listed expressions for each row [1]. Use `MAX` for aggregation and `GREATEST` for row-level comparison.

## References

1. [MAX (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-ver17)
2. [Built-in Aggregate Functions](https://www.sqlite.org/lang_aggfunc.html)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [SQL RANK Function: Syntax, PARTITION BY and Examples](/blog/data-analysis/sql-rank-function)
- [SQL LENGTH Function: Syntax and Examples for String Length](/blog/data-analysis/sql-length-function)
- [SQL LEAD Function: Syntax and Examples](/blog/data-analysis/sql-lead-function)
- [SQL COUNT Function: Syntax, Examples and GROUP BY](/blog/data-analysis/sql-count-function)
- [SQL RIGHT Function: Syntax, Examples and Use Cases](/blog/data-analysis/sql-right-function-syntax-examples)