In this tutorial, we will learn about the MIN() and MAX() aggregate functions. Suppose you want to find the highest-paid employee in your company. Won’t it be very easy if there was a function which you could use directly to get the result? MySQL MAX() does exactly that for you. The MIN() function does the exact opposite i.e. it will tell you the employee with the least salary.
What Are Aggregate Functions?
Before we move on to the implementation of the functions, it is important to understand what aggregate functions are. Aggregate Functions perform operations on multiple values of a column and return a single value. Examples of Aggregate functions are MIN(), MAX(), SUM(), COUNT(), AVG(), etc.
The generic syntax of aggregate functions is as follows:
function_name(expression)Code language: SQL (Structured Query Language) (sql)Where function_name can be any one of the aggregate functions and expression contains column_names.
MAX() is an aggregate function which returns the maximum value in a column. Whereas, MIN() is an aggregate function which returns the smallest value in the given column.
Syntax of MySQL MIN() and MAX()
The syntax for MAX() is as follows,
SELECT MAX(column_name) FROM table_name; Code language: SQL (Structured Query Language) (sql)The syntax for MIN() is as follows,
SELECT MIN(column_name) FROM table_name;Code language: SQL (Structured Query Language) (sql)Note that, you may add clauses like WHERE, LIKE, BETWEEN, IN and so on, in the above syntax.
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,
SELECT MAX(Salary) FROM Employee;Code language: SQL (Structured Query Language) (sql)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,
SELECT MIN(Salary) FROM Employee;Code language: SQL (Structured Query Language) (sql)We get the output as,
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,
SELECT MAX(Salary) FROM Employee WHERE Department='Operations';Code language: SQL (Structured Query Language) (sql)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,
SELECT MIN(Salary) FROM Employee WHERE Department='Executive';Code language: SQL (Structured Query Language) (sql)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,
SELECT MAX(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01';Code language: SQL (Structured Query Language) (sql)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,
SELECT MIN(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01';Code language: SQL (Structured Query Language) (sql)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:
SELECT * FROM Employee WHERE Salary=(SELECT MAX(Salary) FROM Employee);Code language: SQL (Structured Query Language) (sql)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,
SELECT * FROM Employee WHERE Salary=(SELECT MIN(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01');Code language: SQL (Structured Query Language) (sql)And the output is as follows,
Conclusion
Aggregate functions like MIN() and MAX() find a lot of use in data analysis. I would highly recommend you to check out the below reference links.
References
- MySQL official documentation on aggregate functions.