VMware ESXi and vSphere Cluster Management

SQL MAX() Function: Find the Highest Value in a Column

Learn how to use SQL MAX() to find the highest numeric, date, or text value, filter results with WHERE, group maxima with GROUP BY, and retrieve the row containing the maximum.

MAX() is an SQL aggregate function that returns the greatest non-NULL value in a column or expression. It is useful for questions such as “What is the highest product price?” or “What is the latest order date?”

An aggregate function calculates a result from multiple rows. When MAX() is used without GROUP BY, the query normally returns one result for the entire input set.

Basic MAX() Syntax

The general pattern is:

SELECT MAX(column_name)
FROM table_name;

MAX(column_name) is the aggregate expression. The column_name argument identifies the column whose greatest value you want. FROM table_name identifies the source table. SELECT chooses the expression returned by the query.

For example:

SELECT MAX(amount)
FROM products;

Many database systems display an automatically generated column label such as MAX(amount). You can assign a clearer output name with an alias, which is a temporary name for a selected expression:

SELECT MAX(amount) AS highest_amount
FROM products;

Example: Find the Largest Product Amount

Suppose a products table contains this data:

To find the highest amount across every product, run:

SELECT MAX(amount) AS highest_amount
FROM products;

The result is one row:

This query returns the value 399.00, but it does not return the product name or the complete product record.

How MAX() Handles Different Data Types

MAX() compares values according to their data type and the database system’s comparison rules.

Numeric Values

For numeric columns, the result is the mathematically greatest value. Use a numeric column for prices, quantities, scores, salaries, and similar measurements.

Date and Datetime Values

For dates and datetimes, the greatest value is normally the latest one:

SELECT MAX(order_date) AS latest_order_date
FROM orders;

This returns the most recent date present in orders.

Text Values and Collation

MAX() can operate on text, but the result may be less intuitive. Text values are compared according to lexical ordering and the active collation. A collation is a set of database rules that determines how text is compared and sorted.

For example, the maximum value among names may be the name that sorts last alphabetically under the current collation. That does not mean it is the longest, most important, or largest item in a human sense. When measuring size or time, use an appropriate numeric or date column instead.

NULL Behavior

NULL represents a missing or unknown value. It is not the same as zero and is not the same as an empty string.

MAX() ignores NULL values when at least one non-NULL value is available. For example, if a column contains 10, NULL, and 7, the maximum is 10.

If every candidate value is NULL, MAX() returns NULL. A filtered query can also produce NULL when no matching rows provide a non-NULL value.

SELECT MAX(amount) AS highest_amount
FROM products
WHERE category_id = 999;

Check both the filter condition and the source data when a maximum unexpectedly returns NULL. Do not replace NULL with zero unless zero is logically correct for the application.

Filtering Rows Before Calculating the Maximum

WHERE filters rows before aggregation occurs. This lets you calculate a maximum for only the rows that meet a condition.

For example, to find the highest price in category 3:

SELECT MAX(price) AS highest_price
FROM products
WHERE category_id = 3;

The database first keeps products with category_id = 3, then calculates the maximum price among those rows. Without the WHERE clause, the query would calculate the maximum across all products.

Finding the Maximum Value Per Group

GROUP BY partitions rows into groups so an aggregate can be calculated separately for each group. Use it when you need one maximum per department, category, customer, or another grouping.

SELECT department_id,
       MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;

A possible result is:

Each output row represents one department. The query does not return one overall maximum; it returns a maximum for every department.

When selecting a regular column with an aggregate, the regular column generally must appear in GROUP BY. Otherwise, the query may fail or produce unclear results depending on the database system.

MAX() Compared with Related Aggregates

• MAX(): returns the greatest non-NULL value.
• MIN(): returns the smallest non-NULL value.
• COUNT(): counts rows or non-NULL values, depending on the form used.

For example:

SELECT MAX(amount) AS highest_amount,
       MIN(amount) AS lowest_amount,
       COUNT(amount) AS amounts_present
FROM products;

MAX() returns a value, not necessarily the complete row that contains that value. Selecting MAX(amount) alone cannot also identify the product name associated with the amount.

Retrieve the Row Associated with the Maximum

To return product details, first calculate the maximum in a subquery. A subquery is a query nested inside another SQL statement.

SELECT product_id,
       product_name,
       amount
FROM products
WHERE amount = (
    SELECT MAX(amount)
    FROM products
);

The inner query finds the overall maximum amount. The outer query returns products whose amount equals that maximum.

This pattern can return multiple rows when two or more products are tied for the highest amount. That is often useful because it preserves every matching record. If only one row is required, use an explicit tie-breaking rule with your database’s supported ordering and row-limit syntax.

Common Problems and Troubleshooting

The query returns NULL

All candidate values may be NULL, or the WHERE condition may match no usable rows. Check the filter and inspect the source data. Apply a replacement value only when that replacement has a correct business meaning.

The result does not include the product name

MAX() returns only the aggregate value. Use a subquery, a join, or a database-specific ranking query to retrieve rows that match the maximum.

A regular column and MAX() cause an error

A selected non-aggregated column may not be included in GROUP BY. Remove the unrelated column for an overall maximum, add it to GROUP BY for a per-group maximum, or use a row-retrieval pattern.

Text results seem unexpected

Text is compared lexically according to the active collation, not according to numeric size or business importance. Verify the collation or use a numeric or date column that represents the quantity you actually want to measure.

More than one maximum record is returned

Multiple rows share the same maximum value. Keep all tied rows when that is correct, or specify a deterministic tie-breaker if the application requires exactly one row.

Exam-Ready Summary

• MAX() is an aggregate function that returns the highest non-NULL value.
• SELECT MAX(column_name) FROM table_name; normally produces one overall result.
• Use AS to give the result a readable alias such as highest_amount.
• WHERE filters rows before MAX() calculates the result.
• GROUP BY produces one maximum per group.
• Dates use maximum to identify the latest date; text uses lexical and collation-based comparison.
• MAX() does not return the complete row associated with the value. Use a subquery or another row-selection technique.
• Ties can produce multiple rows when retrieving records associated with the maximum.