MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL

MySQL MIN function

The MIN() function determines the minimum or lowest value of the data set. This function works on numerical data type values. If the table displays zero value, then the row does not exist in the table column. The MIN function returns a minimum or least value of the single or multiple columns.

Syntax

The basic MIN () function syntax shows below.

MIN(function_expression)

MySQL MIN () function with the table syntax shows below.

SELECT MIN(function_expression) FROM Table_Name WHERE condition;

Description

  • The MIN() function returns a minimum value of the table.
  • The "function_expression" represents the value of data for function operation.
  • The "WHERE" clause applies conditions on the MIN() function.

Prerequisite for MIN function

  • Create a new table with the below statement.
mysql> create table pen (  

    product_id int NOT NULL,

    product_name varchar(100) NOT NULL,   

    available_quantity int, 

    required_quantity int, 

    Date date

    ); 
  • Insert the data in the table as per data type.
mysql> insert into pen(product_id,

product_name,

available_quantity, required_quantity,

Date)

VALUES   

(1, 'ink pen', 78, 45, '2021-07-01'), 

(2, 'ball pen', 100, 65, '2021-07-01'), 

(3, 'color pen', 78, 45, '2021-08-24'), 

(4, 'brush pen', 80, 50, '2021-11-21'), 

(5, 'craft pen', 95, 55, '2021-07-01'), 

(6, 'sketch pen', 100, 65, '2021-10-13');
  • You can refer to the table structure and information using the SELECT query.
mysql> SELECT * FROM pen;
MySQL MIN() function

Examples of the MIN function

1) Example: The basic MIN function example shows below. Execute the following query to show the minimum number of the available quantity in a table.

mysql> SELECT MIN(available_quantity) AS availability FROM pen;

 Output

MySQL MIN() function

2) Example: The MIN function with the date value example shows below. The following example shows the lowest date of the table.

mysql> SELECT MIN(Date) AS date FROM pen;

Output

MySQL MIN() function

The minimum function returns the first date of the month or year. The smallest and first date of the table is the first day with seven months in the table.

3) Example: The MIN function with the "DISTINCT" and the "WHERE" clauses example shows below. In this example, we use the "DISTINCT" clause with the "available_quantity" column, and the "WHERE" clause executes the "greater than" condition on the "available_quantity" column.

mysql> SELECT MIN(DISTINCT available_quantity) AS availability FROM pen WHERE available_quantity > 80;

 Output

MySQL MIN() function

Executing the query will return the smallest number after the 80's value in the table. This column shows the "95" value as the output of the function.

4) Example: The MIN functions with multiple columns example shows below.

The below query uses the product_id, date, and available_quantity columns to return the minimum value from the table.

mysql> SELECT MIN(product_id) AS number, MIN(Date) AS date, MIN(available_quantity) AS availability 
FROM pen;

Output

MySQL MIN() function

The minimum function works on multiple tables' columns simultaneously. Each column shows its smallest value without disturbing the entire row.

5) Example: The MIN functions with the "WHERE" clause example shows below.

mysql> SELECT MIN(product_id) AS product_id, MIN(Date) AS date, MIN(available_quantity) AS availability 
 FROM pen WHERE product_id < 4;

Output

MySQL MIN() function

6) Example: The MIN functions with the "WHERE" clause and "AND" operator example shows below. Here the "where" clause comes with the "less than" and "greater than" condition, and the "AND" operator works on the "available_quantity" and "product_id" columns.

mysql> SELECT MIN(product_id) AS product_id, MIN(Date) AS date, MIN(available_quantity) AS availability 
 FROM pen WHERE available_quantity < 80 AND product_id < 3;

Output

MySQL MIN() function

The output image displays the value between the "AND" condition. The product_id is "1" because of the "less than 3" condition. The availability shows a "78" value due to the "less than 80" condition.

7) Example: The MIN functions with the "GROUP BY" clause example shows below. This example uses the min() function with the "group by" clause.

mysql> SELECT MIN(product_id) AS product_id, 
MIN(Date) AS date, 
MIN(available_quantity) AS availability 
FROM pen 
GROUP BY available_quantity;

Output

MySQL MIN() function

The GROUP BY clause displays the output as per the grouping of the given columns. The table removes the third and sixth rows of the table. The available_quantity or availability column removes duplicate values and shows only single value rows.

8) Example: The MIN functions with the "GROUP BY" and "HAVING" clause example shows below.

mysql> SELECT MIN(product_id) AS product_id,

mysql> SELECT MIN(product_id) AS product_id, 
MIN(Date) AS date, 
MIN(available_quantity) AS availability 
FROM pen 
GROUP BY available_quantity 
HAVING available_quantity > 80;

Output

MySQL MIN() function

The above image gives two rows as per the "group by" and "HAVING" conditions. The group by clause removes a similar value of the "availability" columns. The having clause displays greater than 80 quantities value of the table.

9) Example: The MIN functions with the "ORDER BY" clause example shows below. In this statement, the "ORDER BY" clauses display in a particular order based on the specified column.

mysql> SELECT MIN(product_id) AS product_id, 
MIN(Date) AS date, 
MIN(available_quantity) AS availability 
FROM pen 
ORDER BY available_quantity ASC;

Output

MySQL MIN() function