What are the SQL MIN() and MAX() functions? When should you use them as aggregate functions, and when should you use them with window functions? We explain using practical examples.
It’s difficult to imagine SQL without the MIN() and MAX() aggregate functions. They are very useful among many data professionals because they allow us to resolve a lot of business problems. In this article, I will explain what each function does and discuss several use cases.
If you want to practice SQL aggregate functions, I recommend our interactive SQL Practice Set course. It contains 88 practical SQL exercises based on real-world problems to help you refresh your SQL skills.
What Is the MIN() Function in SQL?
The SQL standard is supported by most relational database engines and includes several aggregate functions. These aggregate functions compute calculations on numerical data. A Beginner’s Guide to SQL Aggregate Functions discusses all of them in detail; this article focuses on only two of these functions:
First, let’s talk about the
MIN() function. It returns the smallest value in a set of values. The values can come from a numeric column or as the result of an expression returning a numeric value. (Note: The column can come from a table or a view.) It is a scalar function that returns one numeric value.
The syntax of the
MIN() function is presented below:
As you see, this function takes one argument – a column or an expression with numeric values. It’s important that the values are numeric, because the function finds the minimum value among them. For example, from this set of values ...
4, 5, 8, 2, 14
… MIN() returns 2.
What Is the MAX() Function in SQL?
MAX() is an aggregate function that returns a numeric value from a set. The difference is that it returns the largest (maximum) value. The values can come from a column or as the result of an expression that returns a numeric value or values. It is a scalar function that returns one value.
Here is the syntax of the
From this set of values ...
4, 5, 8, 2, 14
MAX() returns 14.
The Sample Data
Before we will start to analyze
MAX() use cases, let’s look at the data we’ll be using in the table
This table stores information about cosmetic products in three categories (the column
category): hair, face, and eye. The names of the products are in the column name. The last two columns present data about when the product was delivered (
delivered_year) and its
price. There are only two years (2020 and 2021) in the column
If you need help with the SQL statements and functions we use in this article, try the SQL Basics Cheat Sheet.
Examples of MIN() and MAX() Use Cases
MAX() as a Single Column
Aggregate functions like
MIN() can be used as a single column in the
SELECT query. For example:
SELECT MIN(price) FROM cosmetics;
Here is the result:
SELECT keyword, we put
MIN() and the column name (in this case,
price). Next is the keyword
FROM and the table name (
cosmetics). The result is the smallest value in the column
price, which is 3.
You can use
MAX() in exactly the same way to find the highest product price:
SELECT MAX(price) FROM cosmetics;
And here is the result:
The maximum price in the table
cosmetics is 22 (the price of the eye cream).
The article How to Find Minimum Values in Columns gives you more examples about how to find the minimum value.
2. Using MIN() and MAX() with Other Columns
A more advanced use of the
MAX() functions is to use them on groups of rows. Here is another query:
SELECT category, MAX(price) AS max_priceFROM cosmeticsGROUP BY category;
It returns the data:
First is the
SELECT keyword with the column name (
category), then the function
MAX() with the column
price as the argument. Then we have
max_price, the alias of the new column created by
Next is the keyword
FROM and the table name. At the end, we have the
GROUP BY clause with the column
category. This determines the groups (i.e. products are grouped by category). For each group, we get its largest value – in this case, the highest price among products in that category. In the “eye” category, the highest price is $22; in the “face” and “hair” categories, it's $21.
The article How to Find Maximum Values in Rows gives you more examples of this function.
3. Using MIN() and MAX() in the Same Query
You can use both the
MAX functions in one
SELECT. If you use only these functions without any columns, you don’t need a
GROUP BY clause.
Below we have a query that implements both functions:
SELECT MIN(price) AS min_price, MAX(price) AS max_priceFROM cosmetics;
This query returns the data:
SELECT, we have the
MIN() function with the
price column argument followed by
MAX() with the same argument and their respective aliases. This returns the minimum price ($3) and maximum price ($22) for all products in the table.
Of course, you can do the same by calculating the minimum and maximum price for each category. However, here you’ll need to use GROUP BY:
SELECT category, MIN(price) AS min_price, MAX(price) AS max_priceFROM cosmeticsGROUP BY category;
Here is the result set:
In this case, the minimum price in the “hair” category is $3 and the maximum price is $21, while in the “eye” category the lowest value is $8 and the highest is $22.
You can learn more about finding minimum and maximum values in the articles How to Find the Minimum Value of a Column in SQL and How to Find the Maximum Value of a Numeric Column in SQL.
4. Using MIN() or MAX() In the HAVING Clause
Another way to use
MAX() is filtering rows according to the value returned by this function – i.e. in the
The query below implements
SELECT category, MAX(price) AS max_priceFROM cosmeticsGROUP BY categoryHAVING MIN(price)>4;
The result set:
SELECT, we have the column
category. Next is the
MAX() function with
price as the argument. For each category, we will calculate the maximum price among products belonging to this category. After
FROM cosmetics comes the
GROUP BY clause with the column
At the end of the query is the
HAVING clause with the
MIN() function. This will find the lowest price among products in each category; if that price is less than 4, it will not be included in the results. Because the minimum price in the “hair” category is $3, it was not displayed in the result set.
Notice that using either function in
HAVING doesn’t require using it (or any aggregate function) in
5. Using MIN() or MAX() In a Simple Window Function
The most advanced use of
MAX() is in a window function. If you aren’t familiar with window functions, read the article SQL Window Function Examples with Explanations.
Here is a simple window function that uses the
SELECT name, price, category, delivered_year, MIN(price) OVER (ORDER BY category DESC) AS min_priceFROM cosmetics;
The first thing you can notice is the list of
delivered_year. Next is the
MIN(price) aggregate function, which finds the lowest value in the
OVER is what makes this a window function; it defines the window. or the set of rows within the query result set. This allows us to calculate an aggregate value for each row in the window. Here,
OVER is paired with
ORDER BY category
DESC (i.e. in descending order); thus, the minimum price is always $3, because the lowest price in the “hair” category is $3, which is lower than the next category minimum of $5.
Here is the result:
6. Using MIN/MAX In a Partitioned Window Function
Next, let’s use
MAX() in a window function that has the
PARTITION BY clause. (If you need a refresher on
PARTITION BY, take a look at the SQL Window Functions Cheat Sheet.) This is the query:
SELECT name, price, category, delivered_year, MIN(price) OVER (PARTITION BY delivered_year ORDER BY category DESC) AS min_priceFROM cosmetics;
This query calculates the minimum price for each partition based on the
delivered_year column and sorts rows by the category.
It returns the following result set:
These results contain the list of cosmetics, including their name, price, category and the delivery year. Each row also contains the minimum price for that delivery year (which is the partition). In each partition, rows are sorted in descending order by category.
In this case, the
OVER clause contains the
ORDER BY and
PARTITION BY clauses.
PARTITION BY delivered_year denotes that the minimum price is calculated separately for each year. The records in each partition (the year the products were delivered) are sorted according to category in descending order (
ORDER BY category
What We’ve Learned About SQL’s MIN() and MAX() Functions
MAX() aggregate functions are very popular. In this article, I’ve explained how to use them in various cases. The main difference between them is that
MIN() finds the minimum value in a set of values, while
MAX() returns the maximum value.
Now that you know how to use MIN() and MAX(), you’re ready to:
- Find the largest/smallest values among all given rows or given groups of rows.
- Compare minimum and maximum values with other rows.
- Use these functions on partitions and on simple window functions.
For more information on the differences between aggregate values and window functions, try our article SQL Window Functions vs. SQL Aggregate Functions: Similarities and Differences.
If you’re a beginner and you’d like to expand your knowledge of SQL, I recommend LearnSQL.com’s SQL Basics course. But if you’re already familiar with basic SQL, you might want to take our Window Functions course to level up your skills.