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.
Syntax:
MIN(expr);
Where expr is an expression.
MySQL Version: 5.6
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:
Sample Output:
mysql> SELECT MIN(book_price) -> FROM book_mast; +-----------------+ | MIN(book_price) | +-----------------+ | 45.00 | +-----------------+ 1 row in set (0.00 sec)
PHP script
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-MIN()- php mysql examples | w3resource</title>
<meta name="description" content="example-MIN()- php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Minimum book price:</h2>
<table class='table table-bordered'>
<tr>
<th>Minimum book price</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT MIN(book_price)
FROM book_mast') as $row) {
echo "<tr>";
echo "<td>" . $row['MIN(book_price)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
JSP script
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-min()</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data = "SELECT MIN(book_price)FROM book_mast";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Minimum book price</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("MIN(book_price)")%></TD>
</TR>
<% } %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>
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:
Sample 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
PHP script
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-MIN-with-group-by- php mysql examples | w3resource</title>
<meta name="description" content="example-MIN-with-group-by- php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Minimum book price:</h2>
<table class='table table-bordered'>
<tr>
<th>Category id</th><th>Minimum book price</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT cate_id, MIN(book_price)
FROM book_mast
GROUP BY cate_id') as $row) {
echo "<tr>";
echo "<td>" . $row['cate_id'] . "</td>";
echo "<td>" . $row['MIN(book_price)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
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:
Sample 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)
PHP script
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-MIN-with-group-by-and-order-by- php mysql examples | w3resource</title>
<meta name="description" content="example-MIN-with-group-by-and-order-by- php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of countries in ascending order, city and minimum no of branches of publishers:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's Country</th><th>Publisher's city</th><th>Minimum number of branches</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT country,pub_city,MIN(no_of_branch)
FROM publisher
GROUP BY country,pub_city
ORDER BY country') as $row) {
echo "<tr>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['pub_city'] . "</td>";
echo "<td>" . $row['MIN(no_of_branch)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
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:
Sample 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)
PHP script
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-MIN-with-having- php mysql examples | w3resource</title>
<meta name="description" content="example-MIN-with-having- php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of countries and minimum no of branches of publishers. Publishers those who have less than ten minimum number of branches are included only:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's Country</th><th>Publisher's city</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT country,MIN(no_of_branch)
FROM publisher
GROUP BY country
HAVING MIN(no_of_branch)<10') as $row) {
echo "<tr>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['MIN(no_of_branch)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
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;
Sample 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)
PHP script
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-MIN-with-distinct- php mysql examples | w3resource</title>
<meta name="description" content="example-MIN-with-distinct- php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of Categories and minimum unique number of pages (of books):</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's Country</th><th>Publisher's city</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT cate_id,MIN(DISTINCT no_page)
FROM book_mast
GROUP BY cate_id') as $row) {
echo "<tr>";
echo "<td>" . $row['cate_id'] . "</td>";
echo "<td>" . $row['MIN(DISTINCT no_page)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
Online Practice Editor:
Previous:
Max() with having
Next:
STD()
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework