MIN() in SQL

📉 The MIN() function is an aggregate function in SQL that returns the smallest value from a specified column. It can be used with numeric values, dates, and text. The MIN() function is widely used to find the lowest price, earliest date, minimum score, shortest value according to sorting rules, and other minimum values in a dataset.

📖 What is MIN()?

The MIN() function scans all selected values in a column and returns the smallest one. It ignores NULL values during the calculation.

Information

MIN() works with numeric, date, and character data types. For text values, the result depends on the database's sorting and collation rules.

📝 Basic Syntax

MIN() Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

📊 Sample Table

Consider the following Students table:

StudentIDNameDepartmentMarksAdmissionDate
101AliceComputer Science922024-01-15
102BobMathematics852024-02-10
103CharliePhysics782024-03-20
104DavidComputer Science952024-04-05
105EvaMathematics882024-05-12

1ī¸âƒŖ Finding the Minimum Numeric Value

Retrieve the lowest marks obtained by any student.

Minimum Marks

SELECT MIN(Marks) AS LowestMarks
FROM Students;

Result:

LowestMarks
78

2ī¸âƒŖ MIN() with WHERE

Use the WHERE clause to find the minimum value within filtered records.

Lowest Marks in Computer Science

SELECT MIN(Marks) AS LowestCSMarks
FROM Students
WHERE Department = 'Computer Science';

3ī¸âƒŖ MIN() with Dates

The MIN() function can return the earliest date in a column.

Earliest Admission Date

SELECT MIN(AdmissionDate) AS FirstAdmission
FROM Students;

4ī¸âƒŖ MIN() with Text

The MIN() function can also be used with character data. The smallest value is determined according to the database's collation and sorting rules.

Alphabetically First Name

SELECT MIN(Name) AS FirstName
FROM Students;

Important

Text comparisons depend on the database's collation settings. Different collations may produce different ordering results.

📊 MIN() with GROUP BY

The GROUP BY clause groups records, and MIN() returns the smallest value for each group.

Lowest Marks by Department

SELECT Department,
       MIN(Marks) AS LowestMarks
FROM Students
GROUP BY Department;

Sample Result:

DepartmentLowestMarks
Computer Science92
Mathematics85
Physics78

📈 MIN() with HAVING

The HAVING clause filters grouped results after the minimum values have been calculated.

Departments with Minimum Marks Above 80

SELECT Department,
       MIN(Marks) AS LowestMarks
FROM Students
GROUP BY Department
HAVING MIN(Marks) > 80;

🔗 MIN() with JOIN

The MIN() function is commonly used with joins to find the minimum value across related tables.

Lowest Course Score per Student

SELECT s.Name,
       MIN(c.Score) AS LowestScore
FROM Students s
JOIN Courses c
ON s.StudentID = c.StudentID
GROUP BY s.Name;

⚠ī¸ MIN() and NULL Values

The MIN() function ignores NULL values. Only valid values are considered when determining the minimum.

Column ValuesMIN() Result
10, 20, NULL, 55

Important

If every value in the selected column is NULL, MIN() returns NULL.

📊 Common Uses of MIN()

PurposeExample
Lowest Marks MIN(Marks)
Lowest Salary MIN(Salary)
Lowest Price MIN(Price)
Earliest Date MIN(OrderDate)
Department-wise Minimum MIN(...) GROUP BY Department

⚖ī¸ MIN() vs MAX()

FunctionPurpose
MIN()Returns the smallest value.
MAX()Returns the largest value.

đŸ’ŧ Real-World Example

A university administrator wants to identify the lowest marks scored in each department.

Department-wise Minimum Marks

SELECT Department,
       MIN(Marks) AS LowestMarks
FROM Students
GROUP BY Department
ORDER BY LowestMarks ASC;

This query generates a report showing the minimum marks for every department, sorted from the lowest value to the highest.

⚠ī¸ Best Practices

Best Practice

Use MIN() to retrieve the smallest value in a dataset, combine it with WHERE for filtered searches, and use GROUP BY to find minimum values within groups. Remember that MIN() ignores NULL values and that text comparisons depend on database collation.

🚀 Key Points to Remember

  • 📌 MIN() returns the smallest value in a column.
  • 📌 It works with numeric, date, and text data types.
  • 📌 It ignores NULL values.
  • 📌 It is commonly used with WHERE, GROUP BY, and HAVING.
  • 📌 It can be combined with joins for advanced queries.
  • 📌 Text results depend on the database's collation and sorting rules.
>>"The MIN() function quickly identifies the smallest value in your data, making it essential for analysis and reporting."

Summary

✅ The MIN() function is an essential SQL aggregate function for finding the smallest value in numeric, date, or text columns. Whether identifying the lowest marks, earliest dates, or minimum prices, it provides valuable insights for reporting, analytics, and business decision-making.