SQL SELECT MIN
The MIN is an aggregate function in SQL which returns the smallest value from the multiple values of the column in the table.
Syntax of SQL Min() Function
In this syntax, we can also use the MIN function with the WHERE clause for selecting the minimum value from the filtered records.
Two Examples of SQL Min Function
Example 1: This example describes how to use the MIN function in SQL.
Let's create a simple table on which the MIN function is to be run.
The following CREATE TABLE statement creates the Bikes_Details table with five fields:
(
Bike_Number INT PRIMARY KEY,
Bike_Model INT,
Bike_Name VARCHAR (50),
Number_of_Bikes INT NOT NULL
Bike_Price INT NOT NULL
) ;
The following INSERT query inserts the record of cars into the Bikes_Details table:
VALUES (2578, 2018, KTM, 3, 1500000),
(9258, 2019, Apache, 2, 3000000),
(8233, 2018, Pulsar, 6, 900000),
(8990, 2018, RX100, 7, 700000),
(9578, 2020, Splender+, 6, 8000000),
(1258, 2021, Bullet, 2, 1500000),
(2564, 2019, Passion, 4, 6000000),
(2155, 2020, Livo, 8, 1800000);
The following SELECT query shows the data of the Bikes_Details table:
| Bike_Number | Bike_Model | Bike_Name | Number_of_Bikes | Bike_Price |
|---|---|---|---|---|
| 2578 | 2018 | KTM | 3 | 900000 |
| 9258 | 2019 | Apache | 2 | 1100000 |
| 8233 | 2018 | Pulsar | 6 | 900000 |
| 8990 | 2018 | RX100 | 7 | 700000 |
| 9578 | 2020 | Splender+ | 6 | 8000000 |
| 1258 | 2021 | Buller | 2 | 1500000 |
| 2564 | 2019 | Passion | 4 | 6000000 |
| 2155 | 2020 | Livo | 8 | 1800000 |
The following query shows the smallest value from the Bike_Number column of the above Bikes_Details table:
Output:
Example 2: This example describes you how to use the MIN function with the WHERE clause.
The following query creates the College_Student_Details table using the CREATE TABLE statement:
(
Student_ID INT NOT NULL,
Student_Name varchar(100),
Student_Course varchar(50),
Student_Age INT,
Student_Marks INT
);
The following SQL queries insert the record of students into the above table using INSERT INTO statement:
INSERT INTO College_Student_Details VALUES (102, Raman, MCA, 24, 98);
INSERT INTO College_Student_Details VALUES (104, Shyam, BBA, 19, 92);
INSERT INTO College_Student_Details VALUES (107, Vikash, B.tech, 20, 78);
INSERT INTO College_Student_Details VALUES (111, Monu, MBA, 21, 65);
INSERT INTO College_Student_Details VALUES (114, Jones, B.tech, 18, 93);
INSERT INTO College_Student_Details VALUES (121, Parul, BCA, 20, 97);
INSERT INTO College_Student_Details VALUES (123, Divya, B.tech, 21, 89);
INSERT INTO College_Student_Details VALUES (128, Hemant, MBA, 23, 90);
INSERT INTO College_Student_Details VALUES (130, Nidhi, BBA, 20, 88);
INSERT INTO College_Student_Details VALUES (132, Priya, MBA, 22, 92); 9);
INSERT INTO College_Student_Details VALUES (138, Mohit, MCA, 21, 9
Let's see the record of the above table using the following SELECT statement:
| Student_ID | Student_Name | Student_Course | Student_Age | Student_Marks |
|---|---|---|---|---|
| 101 | Anuj | B.tech | 20 | 88 |
| 102 | Raman | MCA | 24 | 98 |
| 104 | Shyam | BBA | 19 | 92 |
| 107 | Vikash | B.tech | 20 | 78 |
| 111 | Monu | MBA | 21 | 65 |
| 114 | Jones | B.tech | 18 | 93 |
| 121 | Parul | BCA | 20 | 97 |
| 123 | Divya | B.tech | 21 | 89 |
| 128 | Hemant | MBA | 23 | 90 |
| 130 | Nidhi | BBA | 20 | 88 |
| 132 | Priya | MBA | 22 | 99 |
| 138 | Mohit | MCA | 21 | 92 |
The following MIN query displays the lowest marks above 70 from the Student_Marks column of the College_Student_Details table:
Output:
MIN Function with SQL GROUP BY clause
The MIN Function with GROUP BY clause shows the smallest value in each group from the table.
The syntax to use Group BY Clause with MIN Function is given below:
Example of MIN Function with GROUP BY Clause
Let's take the above College_Student_Details table to understand the concept of MIN function With GROUP BY Clause.
The following query shows the minimum marks of student in each course from the above College_Student_Details table:
Output:
| Student_Course | MIN (Student_Marks) |
|---|---|
| B.tech | 78 |
| MCA | 92 |
| BBA | 88 |
| MBA | 65 |
| BCA | 97 |
