sql_func_max

SQL MAX() Function: Finding the Highest Value

Understanding the MAX() Function

The MAX() aggregate function is a fundamental SQL tool used to retrieve the largest (maximum) value from a specified column within a group of rows. It’s essential for data analysis, reporting, and various decision-making processes.

If applied to a column with no non-NULL values, MAX() returns NULL.

SQL MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition; -- Optional: to filter rows before aggregation

You can also use MAX() with the GROUP BY clause to find the maximum value within each group:

SELECT group_column, MAX(data_column)
FROM table_name
GROUP BY group_column;

Demonstration Database: Northwind

Throughout this tutorial, we’ll use the classic Northwind sample database for our examples. This database simulates a small business’s sales system.

Consider this snippet from the “Products” table:

ProductID
ProductName
SupplierID
CategoryID
Unit
Price


1
Chais
1
1
10 boxes x 20 bags
18.00


2
Chang
1
1
24 - 12 oz bottles
19.00


3
Aniseed Syrup
1
2
12 - 550 ml bottles
10.00


4
Chef Anton's Cajun Seasoning
2
2
48 - 6 oz jars
22.00


5
Chef Anton's Gumbo Mix
2
2
36 boxes
21.35

SQL MAX() Example: Finding the Most Expensive Product

The following SQL statement retrieves the highest price from the “Price” column in the “Products” table. We use AS HighestPrice to give the resulting column a descriptive alias.

SELECT MAX(Price) AS HighestPrice
FROM Products;

This query would return a single row and column containing the maximum price found in the Products table.

Practical Application: This is useful for quickly identifying outliers, setting price ceilings, or understanding the upper range of your product pricing.