SQL MIN command: MySQL on Minimum or lowest value in a field

Minimum value in a numeric field can be collected by applying MIN() SQL command.
We will try MIN sql command and how to use it in our tables. But the best way to use MIN command is to apply it in a numeric field.Here is our table with all the records.

id	name	class	mark
1	John Deo	Four	75
2	Max Ruin	Three	85
3	Arnold	Three	55
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55

You can check SQL MAX command to get maximum or highest value of a range of records.

We will apply the MIN command here like this to the field mark

SELECT MIN( mark ) FROM `student`

MIN(mark)
55

The command will locate the minimum value of the mark field and return. We can define some header like this also.

SELECT MIN(mark) as min_mark FROM `student`

min_mark
55

Minimum mark in each class

Now let us find out what is the minimum mark ( or lowest ) in each class. Here we can use the Group By command to find out the minimum mark obtained by each class

SELECT class, min( mark ) as min_mark FROM `student` GROUP BY class

class	min_mark
Four	55
Three	55

Here minimum mark of each class is displayed. Since we have two class in our table so the sql command has returned two class with lowest mark in each of them. We have to use Group By clause if we ask for the query to return any other field name other than the min. Otherwise system will generate error.

Getting all the details of the record having minimum ( min ) data

Among the students who got the minimum mark? This is done by using SQL Sub query. Like this .

SELECT * FROM `student` WHERE mark=(select min(mark) from student)

Output is here

id	name	class	mark
6	Alex John	Four	55

MIN Query with JOIN

SELECT  a.store, MIN(qty) min_qty, b.product,
b.price * MIN(qty) AS total_price  FROM sales a
 LEFT JOIN products b ON a.p_id = b.p_id GROUP BY store;

store	min_qty	product	total_price
ABC	2	Monitor	150
DEF	1	CPU	55

Details of products and sales table with JOIN query

Getting Min value from a date field

We can get Minimum value from a date field like this.

SELECT MIN(exam_dt) FROM student_mark

There are two exam dates available for each month. We may require the 1st exam date for each month.

SELECT MIN(exam_dt) from student_mark group by month(exam_dt)

You can get SQL Dump of student_mark table here

Using SQL with MIN Query in PHP Script

First we will connect to MySQL database.

Here is the sample code.

<?Php
require "config.php";

$count="SELECT class, MIN( mark ) as min_mark FROM `student` GROUP BY class";

echo "<table>";
echo "<tr><th>class</th><th>max_mark</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[class]</td><td>$row[min_mark]</td></tr>";
}
echo "</table>";
?>

Using SQL with MIN query in Python ( SQLite database )

Sample database and connection object can be found in this source code at Colab.

query="SELECT *  FROM student LIMIT 0,5" # sql
my_data=list(my_conn.execute(query)) # rows of data as list
print(my_data) # display  rows

Role in Data Analysis

Trend Analysis: By comparing the minimum values over different periods, analysts can identify trends, such as seasonal lows in sales or stock levels.
Performance Evaluation: In financial data analysis, the `MIN` function helps in assessing the minimum returns or the worst-case scenarios in investment portfolios.
Operational Insights: Identifying the minimum inventory levels or the least number of resources used in production processes can lead to more efficient resource management.

Integrating with Other SQL Functions

Combination with Aggregate Functions: Often used alongside other aggregate functions like `MAX`, `AVG`, and `SUM` to provide a comprehensive view of the data's distribution.
Group By Clause: When used with the `GROUP BY` clause, `MIN` can determine the minimum values of different categories or groups, providing insights at a granular level.
Window Functions: In more advanced scenarios, `MIN` can be used as a window function to perform calculations across sets of rows related to the current row.

Practical Examples

Financial Analysis: Finding the minimum sale price of a product over a year to understand pricing strategies.
Healthcare Data Analysis: Identifying the minimum length of hospital stays to optimize patient flow and resource allocation.
Retail and E-commerce: Analyzing the minimum order quantity to adjust inventory levels and manage supply chain logistics.

Performance Considerations

Indexing: Proper indexing on columns used with the `MIN` function can significantly improve query performance, especially in large datasets.
Null Values Handling: Being aware of how `MIN` interacts with null values is important. By default, `MIN` ignores null values, but this behavior might need to be considered during data analysis to ensure accurate results.

Conclusion

The SQL MIN function is indispensable in data analysis, providing essential insights into the lower extremes of datasets. Its utility spans across various domains, offering a foundation for decision-making processes based on the minimal values within datasets. When combined with other SQL features and functions, MIN enables analysts to conduct thorough and multifaceted data analysis, driving strategic business decisions and operational efficiencies.

SQL Math References Maximum value Query

Subscribe to our YouTube Channel here

hmds	04-08-2011
This is great, but how would you go about getting the id of the student with the lowest mark in each class in this case. So, to return id class min_mark 3 Four 55 6 Three 55

Avinash Kumar	21-05-2012
select name,price from table where price=(select min(price) from table);

	12-07-2023
Show the details of the person with the lowest ID.

smo1234	26-04-2019
test after removal of js file

SQL MIN Command