SQL Notes
Master SQL MIN() and MAX() aggregate functions. Learn syntax, real-world examples, finding extremes in datasets, handling NULL values, GROUP BY integration, performance optimization, and essential interview questions.
In every dataset, extremes exist.
The lowest salary. The highest price. The earliest date. The most recent transaction.
Finding these boundary values manually from thousands or millions of records would be impractical.
SQL solves this problem elegantly using MIN() and MAX() Functions.
These two aggregate functions are among the most frequently used in data analysis, reporting, and business intelligence systems.
For example:
- What is the lowest-priced product in inventory?
- What is the highest employee salary?
- What is the earliest customer registration date?
- What is the most recent order placed?
- What is the minimum stock level across warehouses?
- What is the maximum temperature recorded this year?
Every organization that works with data needs to answer these types of questions regularly.
Understanding how to use MIN() and MAX() efficiently is essential for every SQL learner, analyst, and database professional.
Why are MIN() and MAX() Important?
Consider a retail company with an inventory database storing thousands of products.
Management frequently needs answers like:
1. What is the cheapest product? (for promotions)
2. What is the most expensive product? (for luxury positioning)
3. When was the earliest product added? (for historical analysis)
4. What is the minimum stock level? (for reordering decisions)
5. What is the maximum possible discount? (for pricing strategy)All these questions can be answered with MIN() and MAX().
Practical Scenarios
Scenario 1: Price Analysis
An e-commerce platform analyzes product pricing:
Without MIN/MAX:
Retrieve 50,000 products
\u2193
Manual comparison
\u2193
Time-consuming
\u2193
Error-proneWith MIN/MAX:
SELECT MIN(Price), MAX(Price) FROM Products;
\u2193
Instant results
\u2193
Accurate
\u2193
EfficientScenario 2: Date Tracking
A bank tracks customer account opening dates:
Questions:
- Oldest customer account?
- Newest customer account?
- How long is the customer history?SELECT MIN(OpeningDate) AS OldestAccount,
MAX(OpeningDate) AS NewestAccount,
DATEDIFF(YEAR, MIN(OpeningDate), MAX(OpeningDate)) AS YearsOfHistory
FROM Customers;MIN() Function Syntax
The general syntax is:
SELECT MIN(column_name)
FROM table_name;Example:
SELECT MIN(Salary)
FROM Employees;This returns the minimum salary across all employees.
Understanding MIN()
MIN() identifies the smallest value in a numeric or comparable column.
Example table:
| EmployeeID | Name | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
| 2 | Priya | 70000 |
| 3 | Amit | 45000 |
| 4 | Neha | 65000 |
Query:
SELECT MIN(Salary)
FROM Employees;Process:
50000 → Candidate
70000 → Larger, skip
45000 → Smallest! (New minimum)
65000 → Larger, skip
Result: 45000Output:
45000
The lowest salary is identified instantly.
MAX() Function Syntax
The general syntax is:
SELECT MAX(column_name)
FROM table_name;Example:
SELECT MAX(Salary)
FROM Employees;This returns the maximum salary across all employees.
Understanding MAX()
MAX() identifies the largest value in a column.
Using the same employee table:
| EmployeeID | Name | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
| 2 | Priya | 70000 |
| 3 | Amit | 45000 |
| 4 | Neha | 65000 |
Query:
SELECT MAX(Salary)
FROM Employees;Process:
50000 → Candidate
70000 → Largest! (New maximum)
45000 → Smaller, skip
65000 → Smaller, skip
Result: 70000Output:
70000
The highest salary is found efficiently.
Using MIN() and MAX() Together
Combining both functions provides a complete picture of value range.
Query:
SELECT MIN(Salary) AS LowestSalary,
MAX(Salary) AS HighestSalary,
MAX(Salary) - MIN(Salary) AS SalaryRange
FROM Employees;Output:
| LowestSalary | HighestSalary | SalaryRange |
|---|---|---|
| 45000 | 70000 | 25000 |
This shows:
- Minimum salary: 45000
- Maximum salary: 70000
- Salary spread: 25000Managers can quickly understand salary distribution.
MIN() and MAX() with WHERE Clause
Filtering narrows results before finding extremes.
Example:
SELECT MIN(Salary) AS LowestSalary,
MAX(Salary) AS HighestSalary
FROM Employees
WHERE Department = 'IT';Process:
Filter Employees
\u2193
Department = 'IT'
\u2193
Find MIN and MAX
\u2193
Return ResultsOutput:
| LowestSalary | HighestSalary |
|---|---|
| 45000 | 50000 |
Only IT department salaries are considered.
MIN() and MAX() with GROUP BY
This is one of the most powerful applications.
Finding extremes within groups answers critical questions.
Example table: Sales
| SalesID | Region | Amount |
|---|---|---|
| 1 | North | 5000 |
| 2 | South | 8000 |
| 3 | North | 6000 |
| 4 | East | 7000 |
| 5 | South | 4000 |
Query:
SELECT Region,
MIN(Amount) AS LowestSale,
MAX(Amount) AS HighestSale,
MAX(Amount) - MIN(Amount) AS Range
FROM Sales
GROUP BY Region;Output:
| Region | LowestSale | HighestSale | Range |
|---|---|---|---|
| North | 5000 | 6000 | 1000 |
| South | 4000 | 8000 | 4000 |
| East | 7000 | 7000 | 0 |
Each region gets its own min/max analysis:
Real-World Example: Product Pricing
E-commerce database with product categories:
Table: Products
| ProductID | Category | Price |
|---|---|---|
| 1 | Electronics | 1500 |
| 2 | Electronics | 3000 |
| 3 | Clothing | 500 |
| 4 | Clothing | 1200 |
| 5 | Books | 200 |
| 6 | Books | 400 |
Business Question: What is the price range for each product category?
Query:
SELECT Category,
MIN(Price) AS MinPrice,
MAX(Price) AS MaxPrice,
MAX(Price) - MIN(Price) AS PriceRange,
COUNT(*) AS ProductCount
FROM Products
GROUP BY Category;Output:
| Category | MinPrice | MaxPrice | PriceRange | ProductCount |
|---|---|---|---|---|
| Electronics | 1500 | 3000 | 1500 | 2 |
| Clothing | 500 | 1200 | 700 | 2 |
| Books | 200 | 400 | 200 | 2 |
Business Insights:
Electronics:
- Budget option: 1500 (Entry-level)
- Premium option: 3000 (High-end)
- Price gap: 1500
Clothing:
- Budget option: 500 (Basic)
- Premium option: 1200 (Designer)
- Price gap: 700
Books:
- Budget option: 200 (Standard)
- Premium option: 400 (Special edition)
- Price gap: 200MIN() and MAX() with Dates
Finding earliest and most recent dates is critical in many scenarios.
Example: Customer transactions
| CustomerID | TransactionDate | Amount |
|---|---|---|
| 1 | 2024-01-15 | 5000 |
| 1 | 2024-06-20 | 3000 |
| 2 | 2024-02-10 | 2000 |
| 2 | 2024-07-05 | 4000 |
Query:
SELECT CustomerID,
MIN(TransactionDate) AS FirstTransaction,
MAX(TransactionDate) AS LastTransaction,
COUNT(*) AS TransactionCount
FROM Transactions
GROUP BY CustomerID;Output:
| CustomerID | FirstTransaction | LastTransaction | TransactionCount |
|---|---|---|---|
| 1 | 2024-01-15 | 2024-06-20 | 2 |
| 2 | 2024-02-10 | 2024-07-05 | 2 |
This reveals:
Customer 1:
- Joined: 2024-01-15
- Last activity: 2024-06-20
- Customer lifetime: 5 months
Customer 2:
- Joined: 2024-02-10
- Last activity: 2024-07-05
- Customer lifetime: 5 monthsNULL Handling in MIN() and MAX()
MIN() and MAX() ignore NULL values.
Example:
| EmployeeID | Name | Bonus |
|---|---|---|
| 1 | Rahul | 5000 |
| 2 | Priya | NULL |
| 3 | Amit | 8000 |
| 4 | Neha | NULL |
Query:
SELECT MIN(Bonus) AS MinBonus,
MAX(Bonus) AS MaxBonus,
COUNT(Bonus) AS NonNullCount
FROM Employees;Output:
MinBonus | MaxBonus | NonNullCount 5000 | 8000 | 2
Behavior:
5000 → Counted
NULL → Ignored
8000 → Counted
NULL → Ignored
Result: MIN=5000, MAX=8000NULL values do not affect extremes.
MIN() and MAX() with String Values
These functions work with string data too.
Example: Product names
| ProductID | ProductName |
|---|---|
| 1 | Apple |
| 2 | Banana |
| 3 | Cherry |
Query:
SELECT MIN(ProductName) AS AlphabeticallyFirst,
MAX(ProductName) AS AlphabeticallyLast
FROM Products;Output:
AlphabeticallyFirst | AlphabeticallyLast Apple | Cherry
String comparison:
A (Apple) → First alphabetically
B (Banana) → Middle
C (Cherry) → Last alphabeticallyPerformance Considerations
MIN() and MAX() are highly optimized in modern SQL databases.
With Indexes
If a column has an index:
CREATE INDEX idx_salary ON Employees(Salary);Finding MIN/MAX becomes extremely fast:
INDEX STRUCTURE:
45000 ← MIN (Left leaf)
50000
65000
70000 ← MAX (Right leaf)
Access time: O(log n) - Very fast!Without Indexes
Full table scan: O(n)
Slower but still acceptableBest Practice:
Comparison Table: MIN vs MAX vs AVG vs COUNT
Understanding when to use each function:
| Function | Purpose | Returns | NULL Handling | Example Use |
|---|---|---|---|---|
| MIN() | Smallest value | Single minimum | Ignores | Lowest price |
| MAX() | Largest value | Single maximum | Ignores | Highest salary |
| AVG() | Average value | Mean of values | Ignores | Average order total |
| COUNT() | Number of rows | Row count | COUNT(*) counts all, COUNT(col) ignores NULL | Total customers |
Interview Q&A Section
Q1: What is the difference between MIN() and MAX()?
A: MIN() returns the smallest value in a column, while MAX() returns the largest.
-- MIN example
SELECT MIN(Price) FROM Products; -- Output: 50
-- MAX example
SELECT MAX(Price) FROM Products; -- Output: 5000Key differences:
| Aspect | MIN() | MAX() |
|---|---|---|
| Purpose | Find minimum | Find maximum |
| Value returned | Lowest | Highest |
| Use case | Floor price, minimum salary | Ceiling price, maximum salary |
Q2: How do MIN() and MAX() handle NULL values?
A: Both functions ignore NULL values completely.
-- Table data
| ID | Value |
|----|-------|
| 1 | 100 |
| 2 | NULL |
| 3 | 50 |
-- Query
SELECT MIN(Value), MAX(Value) FROM Table;
-- Output: MIN=50, MAX=100
-- NULL is not consideredQ3: What is the result of MIN() and MAX() on an empty table?
A: Both return NULL.
-- Empty table
SELECT MIN(Price) FROM Products WHERE Price > 10000;
-- Output: NULL (no rows match, so no values to find)Q4: Can MIN() and MAX() work with string data?
A: Yes! They perform alphabetical/lexicographical comparison.
-- String comparison
SELECT MIN(Name), MAX(Name) FROM Employees;
-- If names: Alice, Bob, Charlie
-- MIN returns: Alice (alphabetically first)
-- MAX returns: Charlie (alphabetically last)Q5: How do you find the product with the minimum price?
A: Use a subquery or a WHERE clause with MIN().
Method 1: Subquery
SELECT ProductName, Price
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);Method 2: WITH (CTE)
WITH MinPrice AS (
SELECT MIN(Price) AS LowestPrice
FROM Products
)
SELECT ProductName, Price
FROM Products
WHERE Price = (SELECT LowestPrice FROM MinPrice);Q6: What does GROUP BY do with MIN() and MAX()?
A: GROUP BY applies MIN() and MAX() to each group separately.
-- Without GROUP BY: Single overall minimum
SELECT MIN(Salary) FROM Employees;
-- Output: 45000 (global minimum)
-- With GROUP BY: Minimum per department
SELECT Department, MIN(Salary) FROM Employees GROUP BY Department;
-- Output:
-- IT | 45000
-- HR | 50000
-- Finance | 60000Q7: How do you find both MIN and MAX in one query?
A: Use both functions in the same SELECT statement.
SELECT MIN(Price) AS LowestPrice,
MAX(Price) AS HighestPrice,
MAX(Price) - MIN(Price) AS PriceRange
FROM Products;Output:
LowestPrice | HighestPrice | PriceRange 50 | 5000 | 4950
Q8: Can you use MIN() and MAX() with HAVING?
A: Yes, HAVING filters groups based on aggregated values.
SELECT Department,
MIN(Salary) AS MinSalary,
MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY Department
HAVING MAX(Salary) > 70000;This shows only departments where maximum salary exceeds 70000.
Q9: What is the performance difference between MIN()/MAX() with and without indexes?
A:
With index: O(log n) - Very fast, reads leaf nodes directly
Without index: O(n) - Slower, scans all rows
For a 1 million row table:
With index:
Time: ~1 millisecond
Operations: ~20 comparisons (log2(1,000,000) ≈ 20)
Without index:
Time: ~100 milliseconds
Operations: 1,000,000 comparisonsRecommendation: Create indexes on columns frequently used with MIN/MAX.
Q10: How do MIN() and MAX() differ from ORDER BY LIMIT?
A:
Using MIN/MAX:
SELECT MIN(Price) FROM Products; -- FastUsing ORDER BY with LIMIT:
SELECT Price FROM Products ORDER BY Price ASC LIMIT 1; -- Slower for large tablesComparison:
| Method | Speed | Best Use |
|---|---|---|
| MIN() | Optimized | Just need the value |
| ORDER BY LIMIT | Slower | Need multiple columns with minimum |
Q11: What happens if you use MIN/MAX on a column with duplicate values?
A: It returns the extreme value, not all instances.
-- Table
| ID | Score |
|----|-------|
| 1 | 85 |
| 2 | 95 |
| 3 | 95 |
-- Query
SELECT MIN(Score), MAX(Score) FROM Scores;
-- Output: MIN=85, MAX=95
-- Returns: 95 (single value, not 95, 95)Q12: How do you find all records with MIN or MAX value?
A: Use a WHERE clause with a subquery.
-- Find all employees with maximum salary
SELECT *
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
-- Find all products with minimum price
SELECT *
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);Advanced Example: Complete Analysis
Real scenario: Analyzing customer spending patterns.
Table: Orders
| OrderID | CustomerID | OrderDate | Amount |
|---|---|---|---|
| 1 | 101 | 2024-01-15 | 5000 |
| 2 | 101 | 2024-03-20 | 3000 |
| 3 | 102 | 2024-02-10 | 8000 |
| 4 | 102 | 2024-07-05 | 2000 |
| 5 | 103 | 2024-04-12 | 4500 |
Comprehensive Query:
SELECT CustomerID,
COUNT(*) AS OrderCount,
MIN(Amount) AS LowestOrder,
MAX(Amount) AS HighestOrder,
MAX(Amount) - MIN(Amount) AS OrderSpread,
AVG(Amount) AS AverageOrder,
MIN(OrderDate) AS FirstOrder,
MAX(OrderDate) AS LastOrder
FROM Orders
GROUP BY CustomerID
ORDER BY MAX(Amount) DESC;Output:
| CustomerID | OrderCount | LowestOrder | HighestOrder | OrderSpread | AverageOrder | FirstOrder | LastOrder |
|---|---|---|---|---|---|---|---|
| 102 | 2 | 2000 | 8000 | 6000 | 5000 | 2024-02-10 | 2024-07-05 |
| 101 | 2 | 3000 | 5000 | 2000 | 4000 | 2024-01-15 | 2024-03-20 |
| 103 | 1 | 4500 | 4500 | 0 | 4500 | 2024-04-12 | 2024-04-12 |
Business Insights:
Customer 102:
- Most active (2 orders)
- Highest order: 8000 (big spender)
- Lowest order: 2000 (budget purchase)
- Spread: 6000 (variable spending)
- Customer tenure: 5 months
Customer 101:
- Active (2 orders)
- Consistent spending: 3000-5000
- Average: 4000
Customer 103:
- New or inactive (1 order)
- Single purchase: 4500
- Needs follow-up for repeat salesSummary
MIN() and MAX() are fundamental SQL functions that:
- Identify Extremes - Find minimum and maximum values instantly
- Support GROUP BY - Apply extremes to groups separately
- Ignore NULL - Work cleanly with missing data
- Work with Various Data Types - Numbers, dates, strings
- Enable Quick Analysis - Answer "what's the highest/lowest" questions efficiently
- Support Complex Queries - Combine with WHERE, HAVING, JOINs, subqueries
Mastering MIN() and MAX() is essential for data analysis and reporting in any SQL environment.
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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, functions, min, max
Related SQL Complete Guide Topics