Previous
Written by: rJTutorial Team | Last Updated:

SQL MAX

The MAX() function is one of the most frequently used aggregate functions in SQL. Its primary job is to scan a specific column and return the highest (maximum) value found. Whether you are looking for the most expensive product in an inventory, the highest test score in a class, or the most recent "Last Login" date for a user, MAX() is the go-to tool.

Developer Tip: While MAX() is most commonly used with numbers, it is also incredibly useful for finding the "latest" date in a series of timestamps or the last item alphabetically in a list of strings.

 

Syntax of SQL MAX

SELECT MAX(column_name)
FROM table_name
WHERE condition;
  • MAX(column_name): This tells the database to find the largest value within this specific column.
  • FROM table_name: Specifies the table you are querying.
  • WHERE condition: (Optional) Allows you to filter the data before the maximum is calculated (e.g., finding the max price only for products in the 'Electronics' category).
Best Practice: Always use the AS keyword to give your result a descriptive alias (like max_price). Without an alias, the output column name might just appear as "MAX(price)" or "No Column Name," which is harder to handle in your application code.

 

Key Points

  • Versatile Data Types: MAX() works on numerical values, dates (returns the newest date), and strings (returns the value closest to 'Z').
  • Ignores NULLs: The function automatically skips NULL values. If a row has no data in that column, it won't be considered.
  • Single Value Return: Unless used with a GROUP BY clause, MAX() will always return exactly one row.
Watch Out: If the column you are querying contains only NULL values, the MAX() function will return NULL.

 

Example of SQL MAX

Let's look at a typical scenario. Imagine you are building an e-commerce dashboard and need to identify your "premium" price point from the following table:

Table: Products

product_id product_name price
1 Apple 2.5
2 Banana 1.2
3 Cherry 3.0
4 Mango 2.8
5 Orange 1.5

To find the highest price from the price column, we run:

SELECT MAX(price) AS max_price
FROM products;

Output:

max_price
3.0

Explanation:

  • The database engine scans every row in the price column. It compares them and returns the highest numeric value, which is 3.0.
Common Mistake: Beginners often try to select the product name alongside the max price like this: SELECT product_name, MAX(price) FROM products;. In most SQL databases, this will throw an error or return incorrect data because the database doesn't know which name to pick for the maximum price without a subquery or a GROUP BY.

 

SQL MAX with Conditions

In real-world applications, you rarely need the maximum of an entire table. Usually, you want to find the maximum within a specific subset of data. For instance, finding the most expensive product within a specific price bracket:

SELECT MAX(price) AS max_price_above_2
FROM products
WHERE price > 2.0;

Output:

max_price_above_2
3.0

Explanation:

  • The WHERE clause acts as a filter first. The database ignores the Banana (1.2) and Orange (1.5) and only looks at Apple, Cherry, and Mango. From that filtered list, it determines that 3.0 is the highest value.

 

SQL MAX with Grouping

The GROUP BY clause is where MAX() becomes truly powerful. It allows you to find the maximum value for different segments of your data simultaneously. For example, if you had a category column, you could find the most expensive item in every category at once.

SELECT category, MAX(price) AS max_price
FROM products
GROUP BY category;

Output:

category max_price
Fruit 3.0

Explanation:

  • The GROUP BY clause organizes the rows into "buckets" based on the category. The MAX() function is then applied to each bucket independently. Since our sample table only contains 'Fruit', we see one result.

 

SQL MAX with DISTINCT

The DISTINCT keyword can be used inside the MAX() function, but its effect is often misunderstood by beginners.

SELECT MAX(DISTINCT price) AS max_distinct_price
FROM products;

Output:

max_distinct_price
3.0

Explanation:

  • MAX(DISTINCT price) tells SQL to first find all unique price values and then pick the largest one. However, the largest value in a set is the same whether duplicates are included or not. Therefore, MAX(DISTINCT price) will always yield the same result as MAX(price).
Developer Tip: You will rarely see MAX(DISTINCT) in production code. It is much more common to use DISTINCT with COUNT() or SUM(), where removing duplicates actually changes the mathematical result.

 

Summary

The MAX() function is an essential tool for data analysis in SQL. It efficiently retrieves the highest value in a dataset while ignoring NULL entries. By combining it with WHERE for filtering and GROUP BY for segmentation, you can answer complex business questions, such as "What was the highest sale today?" or "What is the latest version of this document for each user?"

Previous