How to Use the MySQL MIN() Function and Examples
In this tutorial, you will learn how to use the MySQL MIN() function. The MIN() function is an aggregate function that returns the minimum value in a group of values.
Other aggregate functions in MySQL:
- MAX() function
- SUM() function
- AVG() function
- COUNT() function
If you want to get the maximum value, you can use the MySQL MAX() function.
For example, suppose you have a list of products you are selling, and you want to know which products have the lowest selling price. In this case, you can use the MIN() function.
MySQL MIN() Syntax
The syntax of the MIN() function is as follows:
MIN(expression)
Examples
Below is the demo "orders" table to use in the example:
| order_number | order_date | customer | amount |
|---|---|---|---|
| #ORD_00001 | 2019-11-12 | Gloria Harvey | 2450.50 |
| #ORD_00002 | 2019-10-03 | Annie Mitchell | 2000.00 |
| #ORD_00003 | 2019-10-03 | Grant Daniels | 1890.40 |
| #ORD_00004 | 2019-11-10 | Thomas Pratt | 1370.50 |
| #ORD_00005 | 2019-11-12 | Ronald Houston | 2780.50 |
| #ORD_00006 | 2019-11-12 | Vera Bell | 1290.00 |
| #ORD_00007 | 2019-10-04 | Lindsay Leonard | 1768.00 |
| #ORD_00008 | 2019-10-04 | Mario Simmons | 2340.50 |
Example 1: MIN() without WHERE clause
For example, suppose you want to get the smallest amount in the "Amount" column. You can write a SQL query as follows:
SELECT MIN(orders.amount) AS SmallestAmount
FROM orders;
Result:
This statement returns 1290.00, which is the smallest value.
Example 2: MIN() and WHERE clause
The following SQL statement returns the smallest amount, where order_date is 2019-11-12:
SELECT MIN(orders.amount) AS SmallestAmount
FROM orders
WHERE order_date = '2019-11-12';
Result:
The above query returns 1290.00.
Example 3: MIN() and AND operator
This example shows how to use the MIN() function and the AND operator in a WHERE clause.
The following SQL query returns the minimum value between two dates:
SELECT MIN(o.amount) AS SmallestAmount
FROM orders o
WHERE o.order_date >= '2019-10-01' AND o.order_date <= '2019-10-05';
Result:
MySQL searches for the smallest amount between 2019-10-01 and 2019-10-05 and returns 1768.00.
Summary
In this tutorial, you have learned how to use the MIN() function in MySQL. MIN() is an aggregate function that returns the minimum value in a group. If you want to return the maximum value, use the MySQL MAX() function.