SQL MAX(): Syntax, Usage, and Examples

The MAX() function returns the largest value in a set of rows. You’ll use it any time you need the highest price, the latest date, or the biggest number in a column.

How to Use the MAX() function

MAX() takes a column (or expression) and returns a single highest value.

SELECTMAX(column_name)
FROM table_name;

You can use it with numbers, dates, and even text, as long as your database can compare the values.

Finding the maximum value in a column

SELECTMAX(price)AS highest_price
FROM products;

This returns the highest price in the products table.

Using MAX() with a filter

If you only care about a certain subset of rows, add a WHERE clause.

SELECTMAX(amount)AS largest_completed_order
FROM orders
WHERE status='completed';

This returns the largest order amount among completed orders.

Using MAX() with groups

To find a maximum per customer, category, or region, combine MAX() with GROUP BY.

SELECT category,MAX(price)AS highest_price
FROM products
GROUPBY category;

Now you get one maximum per category.

Using MAX() on an expression

You can also apply MAX() to calculated values.

SELECTMAX(price* quantity)AS biggest_line_total
FROM order_items;

This returns the largest line total in the order_items table.

When to Use the MAX() function

MAX() is useful whenever you need “the biggest one” instead of a full list of results.

1) Finding the latest date or newest record

This is common in activity feeds, log tables, and analytics.

Examples:

• Latest signup date
• Most recent order timestamp
• Last time a user logged in

2) Finding the highest numeric value

This is the classic MAX() use case.

Examples:

• Highest salary
• Largest invoice amount
• Highest test score

3) Finding the biggest value per group

Grouping turns “one max for everything” into “one max for each bucket.”

Examples:

• Top purchase per customer
• Highest product price per category
• Maximum temperature per city

4) Spotting weird data quickly

If a value looks suspiciously high, MAX() helps you find it in seconds.

Examples:

• A product priced at 999999 by mistake
• A user age of 250
• A timestamp that’s far in the future

Examples of the MAX() function

Let’s run through a few realistic examples.

Example 1: Highest product price

SELECTMAX(price)AS highest_price
FROM products;

Result could be something like 129.99.

Example 2: Latest signup date

If your users table has a created_at column:

SELECTMAX(created_at)AS most_recent_signup
FROM users;

This gives you the latest date and time someone created an account.

Example 3: Highest price per category

SELECT category,MAX(price)AS highest_price
FROM products
GROUPBY category;

This is great for quick reporting.

Example 4: Largest order per customer

SELECT customer_id,MAX(amount)AS biggest_order
FROM orders
GROUPBY customer_id;

Now you can see each customer’s “personal best” order total.

Example 5: Largest order in the last 7 days

Time filters make the result more meaningful.

SELECTMAX(amount)AS biggest_order_this_week
FROM orders
WHERE created_at>=CURRENT_DATE-INTERVAL'7 days';

This returns the largest order from the last week.

Example 6: Maximum value with a join

Example: show each customer name and their biggest order.

SELECT c.name,MAX(o.amount)AS biggest_order
FROM customers c
JOIN orders oON o.customer_id= c.id
GROUPBY c.name
ORDERBY biggest_orderDESC;

This is a nice “top spenders” view without needing a complicated query.

Learn More About the MAX() function

MAX() works with numbers, dates, and text

MAX() can compare different data types:

• Numbers: returns the largest number
• Dates: returns the latest date
• Text: returns the “largest” string based on sorting rules (often alphabetical order)

Example with text:

SELECTMAX(username)AS last_username_alphabetically
FROM users;

If your database sorts A → Z, MAX() returns the last value alphabetically.

Text results depend on collation rules, so case sensitivity can change what “largest” means.

MAX() ignores NULL values

NULL values don’t count when calculating the maximum.

Example data:

• 10
• 20
• NULL

MAX() returns 20.

SELECTMAX(score)AS max_score
FROM exams;

If every row is NULL, the result is also NULL.

Using COALESCE() with MAX()

If NULL means “missing,” you might want to treat it as a default value.

SELECTMAX(COALESCE(bonus_points,0))AS max_bonus_points
FROM user_scores;

This counts missing bonus points as 0 instead of skipping them.

MAX() vs ORDER BY + LIMIT 1

You can get the maximum by sorting too:

SELECT price
FROM products
ORDERBY priceDESC
LIMIT1;

That returns the highest price, but there’s a key difference:

• MAX() returns the maximum value only
• ORDER BY ... LIMIT 1 can return the full row (name, category, etc.)

If you want the product name and price, sorting is usually easier:

SELECT name, price
FROM products
ORDERBY priceDESC
LIMIT1;

Getting the row that contains the maximum value

A common goal is “give me the row that has the highest value.”

You can do that with a subquery:

SELECT name, price
FROM products
WHERE price= (SELECTMAX(price)FROM products);

If multiple products share the same highest price, this returns all of them.

MAX() with GROUP BY and HAVING

HAVING filters grouped results after aggregation.

Example: show only categories where the most expensive item costs more than 100.

SELECT category,MAX(price)AS highest_price
FROM products
GROUPBY category
HAVINGMAX(price)>100;

You can’t do this with WHERE because WHERE runs before grouping.

MAX() with CASE for conditional maximums

You can find a maximum inside a subset of rows using CASE.

Example: highest discounted price (ignoring non-discounted rows):

SELECTMAX(
CASEWHEN is_discounted=TRUETHEN priceELSENULLEND
)AS highest_discounted_price
FROM products;

The NULL keeps non-discounted products out of the calculation.

MAX() as a window function

Window functions let you calculate a maximum while keeping all rows visible.

Example: show every order plus the customer’s biggest order value.

SELECT
  customer_id,
  amount,
MAX(amount)OVER (PARTITIONBY customer_id)AS biggest_order_for_customer
FROM orders;

This is super handy for analysis and ranking.

Watch out for joins that duplicate rows

Joins can accidentally multiply rows, which can confuse your results and slow down queries.

Example: joining orders to order_items repeats each order once per item. The maximum might stay the same, but the query could do more work than needed.

If you only need the maximum order amount per customer, it’s often better to calculate it from the cleanest table first, then join.

Summary

MAX() returns the largest value in a column or expression. Use it to find the highest numbers, latest dates, biggest totals, or maximum values per group.

Add WHERE to filter, GROUP BY to split results into categories, and use a subquery or sorting if you need the full row that contains the maximum value.