MySQL MIN

 MySQL MIN

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() the function returns the minimum value in a set of values. The MIN() the 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 is the basic syntax of the MIN function:

MIN(DISTINCT expression);

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

The DISTINCT has no effect on the MIN() function like other aggregate functions such as SUM(), AVG()and COUNT().

MySQL MIN function examples

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

A) Using MySQL MIN() function to find the minimum value in all rows

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

SELECT 
    MIN(buyPrice)
FROM
    products;

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

B) 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';

In this example:

• First, specify a condition in the WHERE the 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:

C) 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);

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.

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

Like other aggregate functions, the MIN() the function is often used with the GROUP BY clause to find the minimum values 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;

In this example:

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

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

This query finds product lines that have the lowest buy prices less than 21:

SELECT 
    productLine, 
    MIN(buyPrice)
FROM
    products
GROUP BY 
    productline
HAVING 
    MIN(buyPrice) < 25
ORDER BY 
    MIN(buyPrice);

F) Using MySQL 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);

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 an 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;

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