MySQL MAX() Function

MySQL MAX() Function: Retrieving the Highest Value

The MySQL MAX() function scans a group of rows and returns the single highest non-NULL value for a given column or expression. Developers use it to find peak order totals, newest timestamps, and category ceilings.

Syntax

SELECT MAX(column_name) AS max_value
FROM table_name
WHERE condition;

When the filtered input has zero rows, MAX() returns NULL rather than 0. Wrap it in COALESCE(MAX(total_amount), 0) if your API expects a numeric zero on empty tables.

Practical SQL Examples

This query finds each customer's highest single order value and most recent purchase date in demo_orders:

SELECT
    customer_id,
    MAX(total_amount) AS largest_order,
    MAX(created_at) AS last_order_at,
    COUNT(*) AS total_orders
FROM demo_orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING MAX(total_amount) >= 100.00;

To pull the full row for the most expensive item in demo_products, compare against a MAX() subquery or window function:

SELECT id, product_name, price
FROM demo_products
WHERE price = (SELECT MAX(price) FROM demo_products);

Common Mistakes and Best Practices

• Filtering Before vs. After MAX(): Put row-level filters (WHERE status = 'paid') inside WHERE, and put aggregate thresholds (HAVING MAX(total_amount) >= 100) inside HAVING.
• Use Indexes for MAX() Per Group: Creating a composite index on (customer_id, total_amount) allows MySQL to perform loose index scans for grouped MAX() queries.

Frequently Asked Questions

Why does MAX() return NULL when there are no matching rows?

Aggregate functions over an empty group return NULL by SQL standard; use COALESCE(MAX(col), 0) if you need zero instead.

Can I pass a CASE expression inside MAX()?

Yes, MAX(CASE WHEN status = 'paid' THEN total_amount END) lets you compute conditional maximums inside a single query pass.

Does MAX() support DISTINCT?

MySQL accepts MAX(DISTINCT col), though it returns the exact same value as MAX(col) because duplicates never change the highest value.