SQL MAX

The MAX() function in SQL is used to retrieve the maximum value from a specified column in a table. It can be applied to numerical, string, or date columns to find the largest value in the dataset.

 

Syntax of SQL MAX

SELECT MAX(column_name)
FROM table_name
WHERE condition;

• MAX(): The aggregate function used to find the maximum value.
• column_name: The name of the column from which to find the maximum value.
• table_name: The name of the table where the data is stored.
• condition: Optional condition to filter rows.

 

Key Points

• MAX() works on numerical, date, and string data types.
• It will ignore NULL values.
• The result will return a single value, representing the maximum value in the column.

 

Example of SQL MAX

Consider the following table:

Table: Products

To find the highest price from the price column:

SELECT MAX(price) AS max_price
FROM products;

Output:

Explanation:

• The MAX(price) function returns the highest value from the price column, which is 3.0 (for Cherry).

 

SQL MAX with Conditions

You can apply a condition to find the maximum value based on a specific criterion. For example, to find the highest price of products that cost more than 2.0:

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

Output:

Explanation:

• The MAX(price) function returns the highest price from the price column where the value is greater than 2.0.

 

SQL MAX with Grouping

You can also use MAX() with the GROUP BY clause to get the maximum value for each group. For example, to find the highest price for each product category (assuming a category column):

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

Output:

Explanation:

• The MAX(price) function finds the highest price for each category. In this example, there is only one category, so the result shows the highest price overall.

 

SQL MAX with DISTINCT

You can use the DISTINCT keyword with MAX() to find the highest distinct value in a column. However, in most cases, MAX() does not need DISTINCT because it already operates on all unique values in the column.

For example, to find the highest distinct price:

SELECT MAX(DISTINCT price) AS max_distinct_price
FROM products;

Output:

Explanation:

• The MAX(DISTINCT price) finds the highest value of distinct prices. In this case, it’s the same result as MAX(price) because all values in the price column are distinct.

 

Summary

The MAX() function in SQL is used to find the maximum value in a specified column. It works on numerical, string, and date columns, and it ignores NULL values. The function can be used with conditions and GROUP BY clauses to refine the query and get the maximum values under different criteria.