Как найти минимальное значение mysql

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 is 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 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 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 product 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 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';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:

C) Using MySQL MIN() with a subquery example

To find 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 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.

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

Like other aggregate functions, the MIN() 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;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.

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

This query finds product lines which 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);Code language: SQL (Structured Query Language) (sql)

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);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 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;Code language: SQL (Structured Query Language) (sql)

Try It Out

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

Introduction to MySQL MIN() Function

MySQL MIN() function is an SQL query that returns the minimum value from a set of values recorded in a database table. Usually, MIN() is an aggregate function that can be used to calculate over a range of values returned by the SELECT query statement to find the minimum record from those separated rows in the table. MIN() function receives a conditional expression that can be a column name or valid term involving columns. The function returns a NULL value if no condition matches. Suppose we can use this function to find out the least population of a city from a list of cities.

Syntax with Explanation:

MIN ( [DISTINCT] Expression)

The term ‘Expression’ requires a numeric value that can either be a field in a table or a formula. This keyword allows for removing the duplicate values from the list of records in the database table. But almost it will be the same result without distinct keywords, too; it has not much effect on MIN () like on other SUM (), COUNT (), or AVG () aggregate functions.

Also,

MIN([DISTINCT] expression) [over_clause]

The over_clause is not compulsory but can be associated with window tasks. So, over_clause is used only if we don’t add the DISTINCT keyword in the MIN() function.

How Does MIN() Function Work in MySQL?

As we know that MIN() function is used to find the minimum value from the total rows selected by the select statement, we can now easily get the benefits in our business or product management. For example, it will allow getting the low-selling product of your company, a less expensive item, or a minimum quantity of the product in your store consumed by the customers.

We can apply this SQL query in our Databases which returns the result. MySQL MIN() SQL statement is shown below:

SELECT MIN ([Column_Name]) FROM [Source]

A database table as a source can return the minimum value present in the specified column by using the MIN() function.

Examples to Implement MIN() in MySQL

Given below are the examples of MIN() in MySQL:

Example #1 – Get minimum value in a column

Let us take the below-shown screenshot of records from a table of Products.

After inspection of all product prices, we applied the MIN() to get the product whose price is lower than others.

SELECT MIN(Price) AS SmallestPrice FROM Products;

Output:

Example #2 – With WHERE clause

Here we use MIN() to return the smallest value from a particular condition using the WHERE keyword with the SELECT statement.

SELECT MIN(Price) AS SmallestPrice FROM Products WHERE SupplierID=2;

Output:

We have the smallest value for the only price whose SupplierID is two from the Products table. We can also use any multi-word alias (like SmallestPrice) to define the lowest value, as in the above query.

Example #3 – Sub-query statement

To provide not only the minimum price from the products table but also to show the row information, including other fields, we have used MIN() in a subquery form as follows:

SELECT * From Products WHERE Price = (SELECT MIN(Price) AS SmallestPrice FROM Products);

Output:

The inner query returns the lowest price, and the outer query provides the row information of the lowest price field in the products table.

Example #4 – With GROUP BY Clause

This function with the GROUP BY clause helps find the minimum value for every group based on the criteria.

SELECT SupplierID , MIN(Price) FROM Products GROUP BY SupplierID;

Output:

SupplierID groups the product fields from the Products table, but the minimum prices are arranged randomly, not ascending or descending.

Example #5 – With ORDER BY clause

This function with the ORDER BY clause helps to specify the column with which we can order by the minimum value for every group based on the criteria.

SELECT SupplierID , MIN(Price) FROM Products
GROUP BY SupplierID
ORDER BY MIN(price);

Output:

Now, the fields are grouped by SupplierID, and the result set is ordered based on MIN(Price) in ascending order, where the first small minimum value is set first and the next greater minimum at last.

Note: We can also use ASC or DESC to set an order BY clause like “ORDER BY MIN(Price) ASC”.

SELECT SupplierID , MIN(Price) FROM Products
GROUP BY SupplierID
HAVING MIN(Price) <50
ORDER BY MIN(price);

SELECT SupplierName, MIN(Price) FROM Products
INNER JOIN Suppliers
USING (SupplierID)
GROUP BY SupplierName
HAVING MIN(Price) <50
ORDER BY MIN(price);

SELECT MIN(CHAR_LENGTH(ProductName)) AS 'Min Char Length'
FROM Products;