MIN()

MIN() Function in MySQL

Introduction
Finding lowest values across datasets is essential for analysis and validation.
MIN() function returns smallest value from non-NULL values in column.
It is widely used for:

• Record dates
• Lowest prices
• Minimum scores

What is MIN() Function
MIN() returns minimum non-NULL value in specified column or expression.
Ignores NULL values automatically.

Basic Syntax

sql

SELECT MIN(column_name) FROM table_name; 

Example

sql

SELECT MIN(marks) as lowest_marks FROM Students; 

Returns lowest mark scored by any student.

MIN with GROUP BY
Minimum per category.

sql

SELECT city, MIN(marks) as lowest_city_mark 

FROM Students 

GROUP BY city; 

MIN with WHERE
Conditional minimums.

sql

SELECT MIN(marks) FROM Students 

WHERE status = 'Active'; 

MIN with Multiple Aggregates
Combine statistics.

sql

SELECT city, 

       MIN(marks) as lowest, 

       MAX(marks) as highest, 

       AVG(marks) as average 

FROM Students 

GROUP BY city; 

MIN with ORDER BY
Sort by minimum values.

sql

SELECT department, MIN(marks) as dept_lowest 

FROM Students 

GROUP BY department 

ORDER BY dept_lowest ASC; 

MIN with JOIN
Minimum across relationships.

sql

SELECT c.course_name, MIN(s.marks) as course_lowest 

FROM Students s 

JOIN Courses c ON s.course_id = c.id 

GROUP BY c.id, c.course_name; 

MIN with HAVING
Filter groups by minimum.

sql

SELECT city, MIN(marks) as lowest_mark 

FROM Students 

GROUP BY city 

HAVING MIN(marks) < 40; 

Multiple MIN Functions
Different criteria minimums.

sql

SELECT city,

       MIN(CASE WHEN status = 'Active' THEN marks END) as active_min,

       MIN(CASE WHEN marks > 0 THEN marks END) as valid_min

FROM Students

GROUP BY city;

MIN with Subquery
Compare against minimum threshold.

sql

SELECT name, marks 

FROM Students 

WHERE marks = (SELECT MIN(marks) FROM Students WHERE marks IS NOT NULL); 

MIN on Dates
Earliest record.

sql

SELECT MIN(join_date) as earliest_join FROM Students; 

MIN on Strings
Lexicographically smallest.

sql

SELECT MIN(city) as first_city FROM Students; 

MIN with COALESCE
Handle all-NULL results.

sql

SELECT COALESCE(MIN(marks), 0) as safe_min FROM Students; 

Execution Order with MIN
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• MIN very fast with indexes
• MIN(DISTINCT) slower
• Index columns used in MIN
• Works well with sorted data

Important Notes

• Ignores NULL values
• Empty set returns NULL
• Works with numbers, dates, strings
• String MIN uses collation order
• MIN(DISTINCT) removes duplicates first

Example Scenario
Course performance extremes:

sql

SELECT

    c.course_name,

    COUNT(s.id) as total_students,

    MIN(s.marks) as lowest_score,

    MAX(s.marks) as highest_score,

    ROUND(AVG(s.marks), 2) as average

FROM Courses c

LEFT JOIN Students s ON c.id = s.course_id

GROUP BY c.id, c.course_name

HAVING MIN(s.marks) IS NOT NULL

ORDER BY lowest_score ASC;

Common Mistakes

• MIN on non-indexed columns (slow)
• Surprised by NULL exclusion
• MIN(DISTINCT) performance overhead
• String collation affecting results
• Forgetting COALESCE for NULL results

Key Points to Remember

• MIN finds smallest non-NULL value
• GROUP BY + MIN = category minimums
• Works with numbers, dates, strings
• Very fast with proper indexes
• Ignores NULLs automatically
• COALESCE(MIN(), default) for safety