SQL Topics
MIN and MAX Functions
title: MIN and MAX Functions
In database systems, businesses often need to identify extreme values within their data. Instead of reviewing every record manually, they usually want quick answers to questions such as:
- What is the highest salary in the company?
- What is the lowest product price?
- Which student scored the highest marks?
- What was the largest order amount?
- What is the earliest joining date?
- What is the most recent transaction date?
Finding these values manually becomes difficult when dealing with thousands or millions of records.
SQL provides two powerful aggregate functions to solve this problem:
- MIN() → Finds the smallest value.
- MAX() → Finds the largest value.
These functions are heavily used in reporting, analytics, business intelligence, dashboards, and performance monitoring systems.
What is the MIN() Function?
The MIN() function returns the smallest value from a column.
Example:
SELECT MIN(Salary)
FROM Employees;Output:
45000This means the lowest salary in the Employees table is ₹45,000.
What is the MAX() Function?
The MAX() function returns the largest value from a column.
Example:
SELECT MAX(Salary)
FROM Employees;Output:
70000This means the highest salary in the Employees table is ₹70,000.
Why are MIN() and MAX() Important?
Imagine a company with:
10,000 EmployeesManagement wants to know:
Highest Salary
Lowest Salary
Highest Bonus
Lowest BonusWithout MIN() and MAX():
Retrieve all records
↓
Sort manually
↓
Identify highest and lowest valuesTime-consuming and inefficient.
With SQL:
SELECT MIN(Salary), MAX(Salary)
FROM Employees;Results are returned instantly.
Basic MIN() Syntax
The general syntax is:
SELECT MIN(column_name)
FROM table_name;Example:
SELECT MIN(Marks)
FROM Students;Returns the lowest marks.
Basic MAX() Syntax
The general syntax is:
SELECT MAX(column_name)
FROM table_name;Example:
SELECT MAX(Marks)
FROM Students;Returns the highest marks.
Understanding the Syntax
Example:
SELECT MAX(Salary)
FROM Employees;SELECT
Specifies the operation.
MAX()
Returns the largest value.
Salary
Column used for comparison.
FROM
Specifies the source table.
Employees
Table containing the data.
The MIN() function works similarly but returns the smallest value.
How MIN() Works Internally
Suppose the Salary column contains:
| Salary |
|---|
| ---------- |
| 50000 |
| 60000 |
| 45000 |
| 70000 |
Query:
SELECT MIN(Salary)
FROM Employees;Internal process:
50000
↓
Compare with 60000
↓
Compare with 45000
↓
Compare with 70000
↓
Smallest Value FoundResult:
45000How MAX() Works Internally
Query:
SELECT MAX(Salary)
FROM Employees;Internal process:
50000
↓
Compare with 60000
↓
Compare with 45000
↓
Compare with 70000
↓
Largest Value FoundResult:
70000Creating a Sample Table
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);Insert records:
INSERT INTO Employees VALUES
(1, 'Rahul', 'IT', 50000),
(2, 'Priya', 'HR', 60000),
(3, 'Amit', 'IT', 45000),
(4, 'Neha', 'Finance', 70000);Finding the Lowest Salary
Query:
SELECT MIN(Salary)
FROM Employees;Output:
45000Employee with the lowest salary earns ₹45,000.
Finding the Highest Salary
Query:
SELECT MAX(Salary)
FROM Employees;Output:
70000Highest salary in the company is ₹70,000.
Using MIN() with WHERE
Example:
SELECT MIN(Salary)
FROM Employees
WHERE Department = 'IT';Process:
Filter IT Employees
↓
Find Lowest SalaryResult:
45000Using MAX() with WHERE
Example:
SELECT MAX(Salary)
FROM Employees
WHERE Department = 'IT';Result:
50000Only IT employee salaries are considered.
MIN() and MAX() with GROUP BY
One of the most common business reporting techniques.
Example:
SELECT Department,
MIN(Salary) AS LowestSalary,
MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department;Result:
| Department | LowestSalary | HighestSalary |
|---|---|---|
| IT | 45000 | 50000 |
| HR | 60000 | 60000 |
| Finance | 70000 | 70000 |
Each department gets its own minimum and maximum values.
Why GROUP BY + MIN/MAX is Useful
Organizations frequently ask:
Highest Sales by Region
Lowest Product Price by Category
Highest Salary by Department
Earliest Order by CustomerGROUP BY combined with MIN() and MAX() provides these insights.
MIN() and MAX() with HAVING
Example:
SELECT Department,
MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department
HAVING MAX(Salary) > 50000;Result:
| Department | HighestSalary |
|---|---|
| HR | 60000 |
| Finance | 70000 |
Only departments meeting the condition are returned.
MIN() and MAX() with Dates
These functions are not limited to numbers.
Example:
SELECT MIN(OrderDate)
FROM Orders;Result:
2025-01-01Returns the earliest order date.
Example:
SELECT MAX(OrderDate)
FROM Orders;Result:
2025-12-31Returns the latest order date.
MIN() and MAX() with Text Values
SQL can also compare text values alphabetically.
Example:
SELECT MIN(Name)
FROM Employees;Result:
AmitAlphabetically first name.
Example:
SELECT MAX(Name)
FROM Employees;Result:
RahulAlphabetically last name.
MIN(), MAX() and NULL Values
Consider:
| Salary |
|---|
| ---------- |
| 50000 |
| NULL |
| 70000 |
Query:
SELECT MIN(Salary)
FROM Employees;Output:
50000Query:
SELECT MAX(Salary)
FROM Employees;Output:
70000Important:
NULL values are ignored.Real-World Example
Imagine an e-commerce platform.
Products table:
| Product | Price |
|---|---|
| Mouse | 500 |
| Keyboard | 1000 |
| Laptop | 60000 |
Query:
SELECT
MIN(Price) AS CheapestProduct,
MAX(Price) AS MostExpensiveProduct
FROM Products;Result:
| CheapestProduct | MostExpensiveProduct |
|---|---|
| 500 | 60000 |
Business instantly understands product price range.
Business Analytics Example
Monthly sales analysis:
SELECT
MONTH(OrderDate) AS Month,
MIN(Amount) AS LowestOrder,
MAX(Amount) AS HighestOrder
FROM Orders
GROUP BY MONTH(OrderDate);Result:
| Month | LowestOrder | HighestOrder |
|---|---|---|
| January | 200 | 15000 |
| February | 500 | 22000 |
Useful for dashboards and performance monitoring.
Performance Considerations
MIN() and MAX() are generally very efficient.
Database engines can often use indexes to locate:
Smallest Value
Largest Valuewithout scanning every row.
This makes these functions extremely fast even on large datasets.
Common Errors
Using MIN() on Unintended Columns
Wrong:
SELECT MIN(Name)
FROM Employees;May return alphabetically smallest value instead of numeric minimum.
Forgetting GROUP BY
Wrong:
SELECT Department,
MAX(Salary)
FROM Employees;Most databases require:
GROUP BY Department;Assuming NULL Values Affect Results
Wrong assumption:
NULL participates in comparison.Reality:
NULL values are ignored.Best Practices
Use Aliases
Good:
SELECT
MIN(Salary) AS LowestSalary,
MAX(Salary) AS HighestSalary
FROM Employees;Combine with GROUP BY
Generates meaningful reports.
Filter Data Before Calculation
Use WHERE whenever possible.
Understand Data Types
MIN() and MAX() work with:
- Numbers
- Dates
- Text
Use Indexes
Indexes can dramatically improve performance.
Common Interview Questions
What does MIN() do?
Returns the smallest value from a column.
What does MAX() do?
Returns the largest value from a column.
Do MIN() and MAX() ignore NULL values?
Yes.
NULL values are ignored.
Can MIN() and MAX() work with dates?
Yes.
They can return the earliest and latest dates.
Can MIN() and MAX() work with text?
Yes.
They compare values alphabetically.
Summary
The MIN() and MAX() functions are essential SQL aggregate functions used to identify the smallest and largest values in a dataset. They help businesses analyze performance, monitor trends, identify extremes, and generate meaningful reports.
In this lesson, you learned:
- What MIN() is
- What MAX() is
- Internal working
- Basic syntax
- Using WHERE
- Using GROUP BY
- Using HAVING
- Working with dates
- Working with text values
- NULL handling
- Real-world examples
- Performance considerations
- Best practices
Mastering MIN() and MAX() is important because identifying extreme values is a common requirement in analytics, reporting, and business intelligence systems.
Next Step
Continue to the next lesson:
String Functions →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for MIN and MAX Functions.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, functions, min
Related SQL Topics