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.

Table of Contents

## 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)
```

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;
```

The syntax for `MIN()`

is as follows,

```
SELECT MIN(column_name) FROM table_name;
```

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;
```

**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;
```

**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';
```

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';
```

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';
```

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';
```

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);
```

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');
```

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.