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.

• Mysql Minimum
• Mysql Min Of Two Values
• Mysql Minus Query
• Mysql Min
• Mysql Min_rows

The MySQL MIN function returns the minimum value of an expression. MySql - Get Min, Max, and 3 random Rows. Left Join query returning wrong row id with correct price-2. Stuck in building MySQL query. Problem in showing the details of the table and their data-3. Properly SQL query. SQL update from one Table to another based on a ID match. In earlier versions of MySQL, enabling this variable caused the server to behave as if the built-in InnoDB were not present, which enabled the InnoDB Plugin to be used instead. In MySQL 5.6, InnoDB is the default storage engine and InnoDB Plugin is not used, so this variable has no effect. As of MySQL 5.6.5, it is ignored. Option 2: Manage partitions automatically using Crontab. Crontab is a good alternative if you are unable to use the MySQL event scheduler. Open crontab file with the command “sudo crontab -e” and add a job for partitioning Zabbix MySQL database (every day at 03:30 AM) by adding this line anywhere in the file.

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.

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:

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

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

Summary: in this tutorial, you will learn how to use the MySQL MAX() function to get the maximum value in a set of values.

Introduction to MySQL MAX() function

The MySQL MAX() function returns the maximum value in a set of values. The MAX() function comes in handy in many cases such as finding the greatest number, the most expensive product, and the largest payment from customers.

Here is the basic syntax of the MAX() function :

Mysql Minimum

If you add the DISTINCT operator, the MAX() function returns the maximum value of distinct values, which is the same as the maximum value of all values. It means that DISTINCT does not take any effects in the MAX() function.

Notice that DISTINCT has effects in other aggregate functions such as COUNT(), SUM(), and AVG().

MySQL MAX() function examples

We’ll use the payments table in the sample database to demonstration the MAX() function.

A) Using MySQL MAX() function to find the maximum value in a column example

This example uses the MAX() function to return the largest amount of all payments:

In this example, the MAX() function checks all values in the amount column of the payments table to find the biggest amount.

B) Using MySQL MAX() function with WHERE clause

This statement uses the MAX() function to find the largest payment in 2004:

In this example:

• First, use a condition in the WHERE clause to get only payments whose year is 2004. We used the YEAR() function to extract year from the payment date.
• Then, use the MAX() function in the SELECT clause to find the largest amount of payments in 2004.

This picture shows the output:

C) Using MySQL MAX() function in subquery example

To get not only the largest payment’s amount but also other payment’s information such as customer number, check number, and payment date, you use the MAX() function in a subquery as shown in the following query:

How it works.

• The subquery returns the largest amount of all payments.
• The outer query gets the payment whose amount is equal to the largest amount returned from the subquery and also other payment’s information.

Another way to do this without using the MAX() function is to sort the result set in descending order using the ORDER BY clause and get the first row in the result set using the LIMIT clause as follows:

If you don’t have an index on the amount column, the second query with the LIMIT clause is faster because it examines all rows in the payments table, while the first query examines all the rows in the payments table twice, first once in the subquery and another in the outer query.

However, if the amount column is indexed, the first query executes faster.

D) Using MySQL MAX() with GROUP BY clause example

Mysql Min Of Two Values

To find the maximum value for every group, you use the MAX function with the GROUP BY clause.

This statement uses the MAX() to get the largest payment of each customer:

In this example:

Mysql Minus Query

• First, the GROUP BY clause group payments into groups by customer number.
• Second, the MAX() function returns the largest payment in each group.

E) Using MySQL MAX() with HAVING clause

When you use the MAX() function with the GROUP BY clause, you can find the maximum value for each group.

If you want to filter groups based on a condition, you can use the MAX() function in a HAVING clause.

The following query finds the largest payment of each customer; and based on the returned payments, get only payments whose amounts are greater than 80,000 .

If you want to see the names of customers instead of numbers, you can join the payments table with the customers table:

Mysql Min

Here is the output:

Mysql Min_rows

In this tutorial, you have learned how to use the MySQL MAX() function to find the maximum value in a set of values.