MySQL MIN() Function
The MySQL MIN() function is used to get the smallest value in a number set. Suppose you have a table with a list of different products and their corresponding prices; you would want to know which one has the lowest price. Here, the MIN() function will return that answer to you in the easiest possible way without searching through all the prices.
This will also help in returning the minimum value in any column, be it prices, ages, scores, or any other numerical data. It eases your work and helps you to produce accurate results within the shortest time.
MySQL MIN() Function
The MySQL MIN() function is used to find the smallest value in a specified column within a table. It's helpful for quickly identifying the minimum numerical, date, or other data type values without manually searching through all the entries.
Syntax of MySQL MIN() Function
SELECT MIN(column_name)
FROM table_name
WHERE condition;
where,
- column_name: The column from which you want to retrieve the least value.
- table_name: str The name of the table containing column.
- condition: This is an optional part. You can put conditions here to filter rows before finding the minimum value.
Examples of MySQL MIN() Function
Create a Table
CREATE TABLE sales (
sale_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
customer_id INT,
amount DECIMAL(10, 2),
sale_date DATE
);
Insert Data into the Table
INSERT INTO sales (product_id, customer_id, amount, sale_date)
VALUES
(101, 201, 300.00, '2024-01-15'),
(102, 202, 150.00, '2024-01-20'),
(101, 203, 200.00, '2024-02-10'),
(103, 204, 450.00, '2024-02-15'),
(102, 205, 100.00, '2024-03-05');
Output:
| sale_id | product_id | customer_id | amount | sale_date |
|---|---|---|---|---|
| 1 | 101 | 201 | 300.00 | 2024-01-15 |
| 2 | 102 | 202 | 150.00 | 2024-01-20 |
| 3 | 101 | 203 | 200.00 | 2024-02-10 |
| 4 | 103 | 204 | 450.00 | 2024-02-15 |
| 5 | 102 | 205 | 100.00 | 2024-03-05 |
Use the MIN() Function
SELECT MIN(product_id) FROM sales;Output:
MIN(Age) |
|---|
101 |
Using MIN() with Conditions
You can also use the MIN() function along with the WHERE clause to filter the records. For example, if you want to get the minimum amount for sales greater than 150, you can use:
Example: Query to Find Minimum Sale Amount Greater Than 150
Now we will be understanding the MIN() function with conditions in MySQL with an example
SELECT MIN(amount) AS minimum_amount
FROM sales
WHERE amount > 150;
Output:
| minimum_amount |
|---|
| 200.00 |
Explanation: In this example, the query returns the smallest sale amount that is greater than 150. The condition 'WHERE amount > 150' filters the records, and the 'MIN(amount)' function finds the smallest amount among the filtered records.
Using MIN() with GROUP BY
The next example will be using the MIN() function with the GROUP BY clause. We will start by creating a sales table, then insert data into the table which is already stored in the database, and finally run the query returning the minimum amount sold of each product.
Example: Query to Find Minimum Sale Amount for Each Product
Now we will be understanding the MIN() GROUP BY function in MySQL with an example:
SELECT product_id, MIN(amount) AS minimum_amount
FROM sales
GROUP BY product_id;
Output:
product_id | minimum_amount |
|---|---|
101 | 200.00 |
102 | 100.00 |
103 | 450.00 |
Explanation: This query groups the sales records by 'product_id' and then applies the MIN() function to find the smallest sale amount for each product.
Conclusion
The MySQL MIN() function is an excellent tool for returning users with the smallest value in any column. It can help you find minimum values for numerical, date, or any other data type very quickly and efficiently. Just learn the syntax and different use cases, and you are good to go in efficiently applying the MIN() function into your SQL queries for valuable insights.
