MySQL MIN() function
MIN() function
MySQL MIN() function returns the minimum value of an expression. MIN() function returns NULL when the return set has no rows. It's a fundamental aggregate function that provides valuable insights into data analysis.
This function is useful in -
- This is essential for understanding the lower limit of a dataset.
- In business analysis, the MIN() function helps evaluate the lowest performance metrics, such as minimum expenses, losses, or user engagement.
- The MIN() function can be used to identify the trough points or trends in a time series dataset, helping to spot periods of lowest activity.
- When comparing performance against benchmarks, the MIN() function assists in determining how close or far data is from the least achieved results.
- For critical decisions, knowing the minimum value in relevant data attributes can provide insights for strategic planning.
- Monitoring data for exceptional cases, such as dropping below a certain threshold or minimum value, can trigger alerts for timely actions.
Syntax:
MIN(expr);
Where expr is an expression.
MySQL Version: 8.0
Contents:
Example : MySQL MIN() function
The following statement will return the minimum 'book_price' from 'book_mast' table.
Sample table: book_mast
Code:
SELECT MIN(book_price)
FROM book_mast;
Relational Algebra Expression:

Relational Algebra Tree:

Output:
mysql> SELECT MIN(book_price) -> FROM book_mast; +-----------------+ | MIN(book_price) | +-----------------+ | 45.00 | +-----------------+ 1 row in set (0.00 sec)
Example: MySQL MIN() function with group by
MySQL MIN() function retrieves the minimum value of an expression which has undergone a grouping operation, if used with GROUP BY clause. The following MySQL statement will extract all "cate_id"s and the minimum 'book_price' for each group of 'cate_id'. GROUP BY clause has grouped "cate_id"s.
Sample table: book_mast
Code:
SELECT cate_id, MIN( book_price)
FROM book_mast
GROUP BY cate_id;
Relational Algebra Expression:

Relational Algebra Tree:

Output:
mysql> SELECT cate_id, MIN(book_price) -> FROM book_mast -> GROUP BY cate_id; +---------+-----------------+ | cate_id | MIN(book_price) | +---------+-----------------+ | CA001 | 85.00 | | CA002 | 105.50 | | CA003 | 45.00 | | CA004 | 88.00 | | CA005 | 50.50 | +---------+-----------------+ 5 rows in set (0.00 sec)
Pictorial Presentation:

Example: MySQL MIN() function with group by and order by
The following MySQL statement will extract those countries ('country') and publisher cities ('pub_city') which have the minimum number of branches ('no_of_branch') for each group of 'country' and 'pub_city'. 'GROUP BY' clause have grouped 'country' and 'pub_city' and the 'country' column have been sorted in ascending order by the usage of ORDER BY clause.
Sample table: publisher
Code:
SELECT country,pub_city,MIN(no_of_branch)
FROM publisher
GROUP BY country,pub_city
Relational Algebra Expression:

Relational Algebra Tree:

Output:
mysql> SELECT country,pub_city,MIN(no_of_branch) -> FROM publisher -> GROUP BY country,pub_city -> ORDER BY country; +-----------+-----------+-------------------+ | country | pub_city | MIN(no_of_branch) | +-----------+-----------+-------------------+ | Australia | Adelaide | 6 | | India | Mumbai | 10 | | India | New Delhi | 10 | | UK | Cambridge | 6 | | UK | London | 8 | | USA | Houstan | 25 | | USA | New York | 10 | +-----------+-----------+-------------------+ 7 rows in set (0.00 sec)
Example: MySQL MIN() function with having
MySQL MIN() function retrieves the minimum value from an expression which has undergone a grouping operation by GROUP BY clause and filtered using HAVING clause followed by some condition. The following MySQL statement will extract those countries ('country') which have ten or less number of branches.
Sample table: publisher
Code:
SELECT country,MIN(no_of_branch)
FROM publisher
GROUP BY country
HAVING MIN(no_of_branch)<10;
Relational Algebra Expression:

Relational Algebra Tree:

Output:
mysql> SELECT country,MIN(no_of_branch) -> FROM publisher -> GROUP BY country -> HAVING MIN(no_of_branch)<10; +-----------+-------------------+ | country | MIN(no_of_branch) | +-----------+-------------------+ | Australia | 6 | | UK | 6 | +-----------+-------------------+ 2 rows in set (0.00 sec)
Example: MySQL MIN() function with distinct
MySQL MIN() function retrieves the unique minimum value of an expression if the function is accompanied by a DISTINCT clause. The following statement will extract category 'cat_id' wise minimum number of page 'no_page' from the 'book_mast' table.
Example:
Sample table: book_mast
Code:
SELECT cate_id,MIN(DISTINCT no_page)>
FROM book_mast
GROUP BY cate_id;
Output:
mysql> SELECT cate_id, MIN(DISTINCT no_page) -> FROM book_mast -> GROUP BY cate_id; +---------+-----------------------+ | cate_id | MIN(DISTINCT no_page) | +---------+-----------------------+ | CA001 | 201 | | CA002 | 300 | | CA003 | 95 | | CA004 | 165 | | CA005 | 88 | +---------+-----------------------+ 5 rows in set (0.00 sec)
Previous:
Max() with having
Next:
STD()
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join