MySQL MIN() Function

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

The 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, or getting the lowest credit limit.

Here’s the basic syntax of the MIN() function:

MIN(DISTINCT expression);Code language: SQL (Structured Query Language) (sql)

In this syntax, the MIN() function accepts an expression that can be a column or a valid expression that involves columns.

The DISTINCT does not have any effect on the MIN() function like other aggregate functions such as SUM(), AVG()and COUNT().

MySQL MIN function examples

We’ll use the products table in the sample database for the demonstration.

1) Using MySQL MIN() function to find the minimum value in a column

This query uses the MIN() function to get the lowest price of all products from the products table:

SELECT
    MIN(buyPrice)
FROM
    products;Code language: SQL (Structured Query Language) (sql)

Try It Out

In this example, the query checks all values in the column buyPrice of the productstable and returns the lowest value.

2) Using MySQL MIN() with a WHERE clause example

This example uses the MIN() function to find the lowest buy price of all motorcycles:

SELECT
    MIN(buyPrice)
FROM
    products
WHERE
    productline = 'Motorcycles';Code language: SQL (Structured Query Language) (sql)

In this example:

• First, specify a condition in the WHERE clause that gets only products whose product line is Motorcycles.
• Second, use the MIN() function to get the lowest value of the buy price of all motorcycles.

Here is the output:

3) Using MySQL MIN() with a subquery example

To find not only the price but also other product information such as product code and product name, you use the MIN() function in a subquery as shown in the following query:

SELECT
    productCode,
    productName,
    buyPrice
FROM
    products
WHERE
    buyPrice = (
        SELECT
            MIN(buyPrice)
        FROM
            products);Code language: SQL (Structured Query Language) (sql)

Try It Out

How it works.

• The subquery returns the lowest buy-price product in the products table.
• The outer query selects the product whose buy price is equal to the lowest price returned from the subquery.

4) Using MySQL MIN() function with a GROUP BY example

Like other aggregate functions, the MIN() function is often used with the GROUP BYclause to find the minimum value for every group.

This example uses the MIN() function with a GROUP BY clause to get the lowest buy price product for each product line:

SELECT
    productline,
    MIN(buyprice)
FROM
    products
GROUP BY productline;Code language: SQL (Structured Query Language) (sql)

Try It Out

In this example:

• First, the GROUP BY clause groups products by product line.
• Second, the MIN() function returns the lowest buy-price product in each product line.

5) Using MySQL MIN() function with a HAVING clause example

This query uses the MIN() function with the GROUP BY and HAVING clauses to find the product lines that have the lowest buy price of less than 21:

SELECT
    productLine,
    MIN(buyPrice)
FROM
    products
GROUP BY
    productline
HAVING
    MIN(buyPrice) < 25
ORDER BY
    MIN(buyPrice);Code language: SQL (Structured Query Language) (sql)

6) Using MIN() with a correlated subquery

The following query selects the lowest-priced 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);Code language: SQL (Structured Query Language) (sql)

Try It Out

In this example, for each product line from the outer query, the correlated subquery selects the lowest-priced product in the product line and returns the lowest price.

The returned lowest price is then used as input for the outer query to find the related product data including product line, product code, product name, and buy price.

If you want to achieve the same result without using the MIN() function and a subquery, you can use a self join as follows:

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;