Summary: in this tutorial, you will learn how to use the MySQL MIN function to find the minimum value in a set of values.
Introduction to MySQL MIN function
MIN function returns the minimum value in a set of values. The
MIN function is very useful in some scenarios such as finding the smallest number, selecting the least expensive product, getting the lowest credit limit, etc.
The following illustrates the syntax of the
If you specify the
DISTINCT operator, the
MIN function returns the minimum value of distinct values, which is the same as omitting the
DISTINCT. In other words,
DISTINCT operator does not have any effect to the
MIN function. It is just for ISO compatibility.
MySQL MIN function examples
Let’s take a look at the
products table in the sample database.
To get the cheapest product in the
products table, which is the product that has the lowest buy price, you use the following query:
SELECT MIN(buyPrice) FROM products;
MySQL MIN with subquery
To select not only the price but also other product’s information such as product code and product name, you use the
MIN function in a subquery as follows:
SELECT productCode, productName, buyPrice FROM products WHERE buyPrice = ( SELECT MIN(buyPrice) FROM products);
How it works.
- The subquery returns the lowest buy price product in the
- The outer query selects the product whose buy price is equal to the lowest price returned from the subquery.
MySQL MIN with GROUP BY
For example, to get the lowest buy price product for each product line, you use the following statement:
SELECT productline, MIN(buyprice) FROM products GROUP BY productline;
If you want to select not only the product line but also other columns in the
products table such as product code and product name, you need to use a correlated subquery.
MySQL MIN with correlated subquery
The following query selects the lowest price product in every product line by combining the
MIN function with a correlated subquery:
SELECT productline, productCode, productName, buyprice FROM products a WHERE buyprice = ( SELECT MIN(buyprice) FROM products b WHERE b.productline = a.productline)
For each product line from the outer query, the correlated subquery selects the lowest price product in the product line and returns the lowest price. The returned lowest price is then used as the input for the outer query to select the related product data including product line, product code, product name and buy price.
SELECT a.productline, a.productCode, a.productName, a.buyprice FROM products a LEFT JOIN products b ON a.productline = b.productline AND b.buyprice < a.buyprice WHERE b.productcode IS NULL;
In this tutorial, you have learned how to use the MySQL MIN function to find the minimum value in a set of values.