MAX¶

Returns the maximum value from all non-NULL values in a group.

Syntax¶

Parameters¶

MAX(expression): Returns the largest non-NULL value of expression in the group. NULL values are ignored.

Returns¶

Returns the maximum value with the same type as expression. If all values are NULL or the input set is empty, returns NULL.

Examples¶

Setup¶

For these examples, assume we have a sales table:

CREATE TABLE sales(
    id BIGINT,
    product STRING,
    region STRING,
    amount BIGINT,
    PRIMARY KEY(id))

INSERT INTO sales VALUES
    (1, 'Widget', 'North', 100),
    (2, 'Widget', 'South', 150),
    (3, 'Gadget', 'North', 200),
    (4, 'Gadget', 'South', NULL),
    (5, 'Widget', 'North', 120)

MAX - Maximum Value¶

Find the maximum amount in the table:

SELECT MAX(amount) AS max_amount FROM sales

`max_amount`
`200`

Notice that the NULL value in row 4 is ignored.

MAX with GROUP BY¶

Find maximum amounts per product:

SELECT product, MAX(amount) AS max_amount
FROM sales
GROUP BY product

`product`	`max_amount`
`"Widget"`	`150`
`"Gadget"`	`200`

Find maximum amounts per region:

SELECT region, MAX(amount) AS max_amount
FROM sales
GROUP BY region

`region`	`max_amount`
`"North"`	`200`
`"South"`	`150`

The South region maximum only considers the non-NULL value (150), ignoring the NULL from the Gadget sale.

Important Notes¶

Index Requirement: GROUP BY queries require an appropriate index to execute. See Indexes for details on creating indexes that support GROUP BY operations.