How to Use the MIN() Function in MySQL

08/04/2021

Contents

In this article, you will learn how to use the MIN() function in MySQL.

Using the MIN() function in MySQL

The MIN() function in MySQL is used to return the minimum value of a column in a table. It is an aggregate function, meaning that it operates on a set of rows to produce a single value.

Syntax

The syntax for the MIN() function is as follows:

SELECT MIN(column_name)
FROM table_name;

Here, column_name is the name of the column for which we want to find the minimum value, and table_name is the name of the table that contains the column.

Examples

Let’s say we have a table named “sales” that contains the sales data for a company. Here is an example of what the table might look like:

+----+-------------+-------+
| id | salesperson | sales |
+----+-------------+-------+
| 1  | John        | 500   |
| 2  | Jane        | 1000  |
| 3  | Bob         | 750   |
| 4  | Sarah       | 300   |
+----+-------------+-------+

To find the minimum sales amount in the “sales” table, we can use the following query:

SELECT MIN(sales)
FROM sales;

The result will be:

+------------+
| MIN(sales) |
+------------+
| 300        |
+------------+

In this example, the MIN() function is used to find the minimum value in the “sales” column of the “sales” table.

Using MIN() with WHERE

We can also use the MIN() function with the WHERE clause to find the minimum value of a column based on a condition. Here is an example:

SELECT MIN(sales)
FROM sales
WHERE salesperson = 'Jane';

The result will be:

+------------+
| MIN(sales) |
+------------+
| 1000       |
+------------+

In this example, the MIN() function is used with the WHERE clause to find the minimum sales amount for the salesperson named Jane.

Using MIN() with GROUP BY

We can also use the MIN() function with GROUP BY to find the minimum value of a column for each group. Here is an example:

SELECT salesperson, MIN(sales)
FROM sales
GROUP BY salesperson;

The result will be:

+-------------+------------+
| salesperson | MIN(sales) |
+-------------+------------+
| John        | 500        |
| Jane        | 1000       |
| Bob         | 750        |
| Sarah       | 300        |
+-------------+------------+

In this example, the MIN() function is used with GROUP BY to find the minimum sales amount for each salesperson in the “sales” table.

Using MIN() with subquery

We can also use the MIN() function with a subquery to find the minimum value of a column based on the result of another query. Here is an example:

SELECT salesperson, sales
FROM sales
WHERE sales = (
    SELECT MIN(sales)
    FROM sales
);

The result will be:

+-------------+-------+
| salesperson | sales |
+-------------+-------+
| Sarah       | 300   |
+-------------+-------+

In this example, the MIN() function is used in a subquery to find the minimum sales amount in the “sales” table, and then used in the WHERE clause of the outer query to find the salesperson with that minimum sales amount.

#MySQL