What is the MySQL MIN() and MAX() Functions?
The MIN() function returns the smallest value of the selected column .The MAX() function returns the largest value of the selected column.
MySQL MAX() Function
In MySQL, the MAX() function is used to return the maximum value of given columns in query. If you need maximum value of the column from the database table, at that time we use this MAX function of MySQL.
Syntax
The basic syntax of MySQL MAX function is :-
SELECT MAX (column_name)
FROM table_name
[WHERE conditions]; Params of MAX function
- column_name :- This database is the column name in the table, you want get the MAXIMUM value of given column.
- table_name :- This is the table name of the database, where you want to retrieve records.
- WHERE conditions :- It is optional. This specifies the conditions that must be matched.
MySQL MIN() Function
In MySQL, the MIN() function is used to return the mininum value of given columns in query. If you need minimum value of the column from the database table, at that time we use this MIN function of MySQL.
Syntax
The basic syntax of MySQL MIN function is :-
SELECT MIN (column_name)
FROM table_name
[WHERE conditions]; Params of MIN function
- column_name :- This database is the column name in the table, you want get the MINIMUM value of given column.
- table_name :- This is the table name of the database, where you want to retrieve records.
- WHERE conditions :- It is optional. This specifies the conditions that must be matched.
Demo Database
Below is a selection from the “Products” table in the Northwind sample database:
| ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
|---|---|---|---|---|---|
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 2 | Chang | 1 | 1 | 24 – 12 oz bottles | 19 |
| 3 | Aniseed Syrup | 1 | 2 | 12 – 550 ml bottles | 10 |
| 4 | Chef Anton’s Cajun Seasoning | 2 | 2 | 48 – 6 oz jars | 22 |
| 5 | Chef Anton’s Gumbo Mix | 2 | 2 | 36 boxes | 21.35 |
MIN() Example
The following SQL statement finds the price of the cheapest product:
Example
SELECT MIN(Price) AS SmallestPrice FROM Products;
MAX() Example
The following SQL statement finds the price of the most expensive product:
Example
SELECT MAX(Price) AS LargestPrice<br>FROM Products;
Some examples of MySQL MIN() and MAX()
Consider the below Employee table.
1. Simple Example of MySQL MIN() and MAX()
Let us find out the maximum salary paid to any employee in the employee table. We do so using the below query,
| 1 | SELECT MAX(Salary) FROM Employee; |
And we get the output as,
As you can see, the output tells us the maximum value in the Salary column, which is the highest amount paid as salary in the company. How about finding the least value in the salary column? The query is,
| 1 | SELECT MIN(Salary) FROM Employee; |
2. Using MySQL MIN() and MAX() with the WHERE Clause
Let us now use the MySQL MIN() and MAX() functions with the WHERE clause. How about finding the highest salary in the Operations department? We use the following query,
| 1 | SELECT MAX(Salary) FROM Employee WHERE Department='Operations'; |
The WHERE clause filters through the table to extract only those rows with ‘Operations’ as the value in the Department column. Then, the MAX() function finds out the highest value in the Salary column among the rows extracted by the WHERE clause. We get the output as follows,
Now, how about finding the least salary in the Executive department? We use the below query,
| 1 | SELECT MIN(Salary) FROM Employee WHERE Department='Executive'; |
And we get the output as,
3. Using MySQL MIN() and MAX() With the BETWEEN Clause
Let us now use the BETWEEN Operator with the MAX() function. What is the highest salary for all employees who joined the company between 1st January 2019 and 1st November 2020? We use the following query,
| 1 | SELECT MAX(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01'; |
And we get the output as,
How about finding the lowest salary of employees who joined the company in the above duration. We use the following query,
| 1 | SELECT MIN(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01'; |
The output is as follows,
4. Using MIN() and MAX() in Subqueries
We can use MAX() in MySQL subqueries. Let me demonstrate this with an example. Let us retrieve records of all the highest-paid employees in the company. We do so using:
| 1 | SELECT * FROM Employee WHERE Salary=(SELECT MAX(Salary) FROM Employee); |
The maximum value gets retrieved by the inner query. This is used in the condition of the WHERE clause and only those rows are returned which satisfy the condition.
We get the output as,
Now, how about retrieving the record of those employees who have the least salary and have joined the company between 1st January 2019 and 1st November 2020. We do so using the query,
| 1 | SELECT * FROM Employee WHERE Salary=(SELECT MIN(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01'); |
And the output is as follows,
