The MIN() function in MySQL is used to return the minimum value in a set of values from the table. It is an aggregate function that is useful when we need to find the smallest number, selecting the least expensive product, etc.
Syntax
The following is the basic syntax of MIN() function in MySQL:
SELECT MIN ( DISTINCT aggregate_expression)
FROM table_name(s)
[WHERE conditions]; Parameter explanation
This function uses the following parameters:
aggregate_expression: It is the required expression. It specifies the column or expression name from which the minimum value will be returned.
Table_name(s): It specifies the tables from where we want to retrieve records. There must be at least one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the records to be selected.
DISTINCT: It allows us to return the minimum of the distinct values in the expression. However, it does not affect the MIN() function and produces the same result without using this keyword.
MySQL MIN() Function Example
Let us understand how MIN function works in MySQL with the help of various examples. Consider our database has a table named “employees” that contains the following data.
1. Basic Example
Execute the following query that uses the MIN function to find the minimum income of the employee available in the table:
mysql> SELECT MIN(income) AS Minimum_Income FROM employees; Output
The above query produces the result of minimum values in all rows. After execution, we will get the output as below:
2. MySQL MIN() Function with WHERE Clause
The WHERE clause allows us to filter the result from the selected records. The following statement finds the minimum income in all rows from the employee table and WHERE clause specifies all those rows whose emp_age column is greater than or equal to 32 and less than or equal to 40.
mysql> SELECT MIN(income) AS Minimum_Income
FROM employees
WHERE emp_age >= 32 AND emp_age <= 40; Output
The above statement will get the output as below:
3. MySQL MIN() Function with GROUP BY Clause
The GROUP BY clause allows us to collect data from multiple rows and group it based on one or more columns. For example, the following statement uses the MIN() function with the GROUP BY clause to find the minimum income in all rows from the employee table for each emp_age group.
mysql> SELECT emp_age, MIN(income) AS Minimum_Income
FROM employees
GROUP BY emp_age; Output
After the successful execution, we can see that the income of each employee returns by grouping them based on their age:
4. MySQL MIN() Function with HAVING Clause
The HAVING clause is always used with the GROUP BY clause to filter the records from the table. For example, the below statement returns the minimum income of all employees, grouping them based on their city and returns the result whose MIN(income)>150000.
mysql> SELECT city, MIN(income) AS Minimum_Income
FROM employees
GROUP BY city
HAVING MIN(income) > 150000; Output
This statement will return the output as below:
5. MySQL MIN() Function with DISTINCT Clause
MySQL uses the DISTINCT keyword to remove the duplicate rows from the column name. We can also use this clause with MIN() function to return the minimum income value of a unique number of records present in the table.
Execute the following query that removes the duplicate records in the income column of the employee table, group by city, and then returns the minimum value:
mysql> SELECT emp_name, city, MIN(DISTINCT income) AS Minimum_Income
FROM employees
GROUP BY city; Output
This statement will give the output as below:
Leave a Reply