• Mysql Minimal
• Mysql Minus Query
• Mysql Min Value
• Mysql Min Function
• Mysql Min Date
• Mysql Min Date

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?

MySQL MIN is one of the Aggregate Functions, which is to find the minimum value of total rows (or records) selected by the SELECT Statement. For example, If you want to find the least performing product in your Store, then you can simply use this Minimum function. The MIN function returns the minimum value in a set of values. Note: See also the MAX function.

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:

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,

The syntax for MIN() is as follows,

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,

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,

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,

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,

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,

And we get the output as,

Mysql Minimal

How about finding the lowest salary of employees who joined the company in the above duration. We use the following query,

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:

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,

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.

This lesson will teach you how to use the aggregate function MIN(). If you missed the Aggregate Introduction Lesson, you might want to checkit out to learn about the GROUP BY statement and its use with MySQL aggregate functions.

You can download the table used in this example, products.sql, from our website. A SQL file can be run through your MySQL administrator interface to create the table.

However, if you would like to create the table with PHP/MySQL, check out our Create a MySQL Table and Insert a MySQL Row lessons.

Products Table:

The MIN function is an aggregate function that findsthe smallest value in a group. The products table that is displayed above has several products of various types. One use of MIN might be to find out thecheapest item in each group.

Mysql Minus Query

Just as we did in the Aggregate Introduction Lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. The column that will have the MIN functionapplied to it is, of course, price.

Display:

Mysql Min Value

Mysql Min Function

Mysql Min Date

If you would rather download the PDF of this tutorial, check out our MySQL eBook from the Tizag.com store. You may also be interested in getting the PHP eBook

Mysql Min Date

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!