SQL online course

SQL MAX() Function

Learn how to use the SQL MAX() aggregate function to return the highest value in a column, with examples using a products table.

The SQL MAX() function returns the highest value found in a column or expression. It is an aggregate function, which means it processes values from multiple rows and produces a summarized result.

A column is a named field in a table, while a table is a collection of rows. When MAX() is used without grouping, the query returns one aggregate value for all qualifying rows.

Basic MAX() Syntax

SELECT MAX(column_name)
FROM table_name;
  • SELECT specifies the value or expression to return.
  • MAX(column_name) examines the values in the selected column and returns the highest one.
  • column_name is the column whose values you want to evaluate.
  • FROM table_name identifies the table that supplies the rows.

The query result is a result set: the rows and columns returned by an SQL query. With no grouping, this MAX() query normally produces a result set containing one row.

Example: Find the Greatest Product Amount

Suppose a products table contains product information like this:

product_id | product_name | amount
-----------+--------------+---------
1          | Keyboard     | 49.99
2          | Monitor      | 349.00
3          | Workstation  | 120166.58

To find the greatest value in the amount column, use:

SELECT MAX(amount)
FROM products;

The database returns a one-row result set. The database system may choose a generated column label:

MAX(amount)
-----------
120166.58

For a clearer column name, assign an alias with AS:

SELECT MAX(amount) AS max_amount
FROM products;
max_amount
----------
120166.58

Here, 120166.58 is the highest stored amount in the table. The alias max_amount describes what the returned value represents. For more on aliases, see SQL aliases.

How MAX() Interprets the Result

MAX() returns the highest value among the rows being evaluated. If the query includes a filtering condition, MAX() evaluates only the rows that meet that condition.

SELECT MAX(amount) AS max_amount
FROM products
WHERE amount > 0;

Without a GROUP BY clause, the result is a single summarized value rather than one result for each product.

MAX() Does Not Return the Whole Matching Row

MAX() returns the maximum value for the expression supplied to it. It does not automatically return the product ID, product name, or other columns from the row where that value appears.

For example, this query returns only the greatest amount:

SELECT MAX(amount) AS max_amount
FROM products;

If you need details from the product with the greatest amount, you need a separate query pattern that identifies the matching row. Do not assume that adding other unaggregated columns to the same query will automatically select the correct row; the exact solution can depend on the SQL task and database system.

MAX() Versus Descending Sort Order

MAX() summarizes values into one maximum result. Sorting with ORDER BY amount DESC arranges all qualifying product rows from largest amount to smallest amount.

SELECT product_id, product_name, amount
FROM products
ORDER BY amount DESC;

The sorted query can return many rows, while the MAX() query returns the highest value as one aggregate result. Learn more about sorting with SQL ORDER BY.

Comparing MAX(), MIN(), and COUNT()

MAX() is one of several SQL aggregate functions:

  • MAX() returns the highest value.
  • MIN() returns the smallest value.
  • COUNT() counts rows or values, depending on the expression supplied.

For example, compare the lower and upper amounts in the products table:

SELECT MIN(amount) AS min_amount,
       MAX(amount) AS max_amount
FROM products;
min_amount | max_amount
-----------+-----------
49.99      | 120166.58

This query still returns one row, but it contains two summarized values. Read more about the SQL MIN() function and SQL COUNT() function.

Common Problems and Troubleshooting

Expecting All Columns from the Largest Row

MAX() returns only the maximum value of the specified expression. It does not return all columns from the row containing that value. Use a suitable row-selection query when product details are required.

Confusing MAX() with Sorting

A descending sort displays rows in order; MAX() calculates a summary. Use MAX() when you need the upper extreme as a value, and use ORDER BY when you need rows arranged from largest to smallest.

Choosing the Wrong Column

Check that the argument passed to MAX() is the value whose upper limit you want. For a product price or amount, use amount, not an identifier such as product_id or a descriptive field such as product_name.

Key Points

  • MAX() is an aggregate function that processes values from multiple rows.
  • It returns the highest value in a selected column or expression.
  • SELECT MAX(column_name) FROM table_name; is the basic pattern.
  • Without grouping, MAX() produces one aggregate value in the result set.
  • The result is the maximum value, not automatically the complete row containing it.
  • MIN() finds the smallest value, while COUNT() counts rows or values.