MySQL Select MIN()

In SQL language, the MIN() aggregation function is used to return the minimum value of a column in a recordset. The function can be applied to numeric or alphanumeric data. For example, it is possible to search for the cheapest product in a table of an online store.
 

Syntax:

The syntax of the SQL query to return the minimum value of the column “column_name” is the following:

SELECT MIN(column_name) FROM table

When this function is used in combination with the GROUP BY command, the query may look like the example below:

SELECT column1, MIN(column2)
FROM table
GROUP BY column1

 

 

Example :

Let’s imagine an e-commerce site that has a “product” table on which you can find computer and furniture products.

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | Monitor  | computer   |  45   | 100     |
|  102 | CD-ROM   | computer   |  100  | 40      |
|  103 | Modem    | computer   |  12   | 20      |
|  104 | Speaker  | computer   |  11   | 3       |
|  105 | Pencil   | furniture  |  148  | 2       |
|  106 | Gum      | furniture  |  250  | 1       |
+------+----------+------------+-------+---------+

 

Find the smallest value:

To get only the smallest price in the table, you can use the following query:

SELECT MIN(price) FROM products

The result is as follows:
 
Output:

+----------------------------------------+
| MIN(price)                             |
+----------------------------------------+
| 1                                      |
+----------------------------------------+

 

 

Find out which record has the smallest value

It is also possible to display the name of the cheapest product at the same time as displaying its price. To do this, simply use the MIN() aggregation function while selecting the other columns. The SQL query is then the following:

SELECT id, name, MIN(price) FROM products

The result is as follows:
 
Output:

+------+----------+---------+
|  id  |   name   |  price  |
+------+----------+---------+
|  106 |   Gum    | 1       |
+------+----------+---------+

 

MIN() function with a GROUP BY:

It is possible to know the min price for each category. This is done using the following query:

SELECT `category`, MIN(price)
FROM `products`
GROUP BY `category`

The result is as follows:
 
Output:

+-----------+------------+
| category  | MIN(price) |
+-----------+------------+
| computer  | 3          |
| furniture | 1          |
+-----------+------------+

 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More