MIN() & MAX() Functions
Learn how to find the smallest and largest values from a column using MIN() and MAX() in SQL Server.
What are MIN() and MAX()?
MIN() and MAX() are aggregate functions in SQL Server.
The MIN() function returns the smallest value from a column, while the MAX() function returns the largest value.
MIN() → Finds the lowest value.
MAX() → Finds the highest value.
Syntax
MIN() Syntax
SELECT MIN(column_name)
FROM table_name;
MAX() Syntax
SELECT MAX(column_name)
FROM table_name;
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Rahul | IT | 50000 |
| 2 | Priya | HR | 40000 |
| 3 | Amit | IT | 60000 |
| 4 | Neha | HR | 45000 |
| 5 | Rohit | Sales | 35000 |
MIN() Function
The MIN() function returns the smallest value from the specified column.
For example, to find the lowest salary:
SELECT MIN(Salary) AS MinimumSalary
FROM Employees;
Output
| MinimumSalary |
|---|
| 35000 |
Therefore, 35000 is the lowest salary in the Employees table.
MAX() Function
The MAX() function returns the largest value from the specified column.
For example, to find the highest salary:
SELECT MAX(Salary) AS MaximumSalary
FROM Employees;
Output
| MaximumSalary |
|---|
| 60000 |
Therefore, 60000 is the highest salary in the Employees table.
MIN() and MAX() Together
We can use MIN() and MAX() in the same query.
SELECT
MIN(Salary) AS MinimumSalary,
MAX(Salary) AS MaximumSalary
FROM Employees;
Output
| MinimumSalary | MaximumSalary |
|---|---|
| 35000 | 60000 |
MIN() with WHERE
WHERE can be used to find the minimum value from records that satisfy a condition.
For example, find the lowest salary in the IT department:
SELECT MIN(Salary) AS MinimumSalary
FROM Employees
WHERE Department = 'IT';
The IT department has salaries of 50000 and 60000, so the minimum salary is 50000.
MAX() with WHERE
WHERE can also be used with MAX() to find the highest value for specific records.
SELECT MAX(Salary) AS MaximumSalary
FROM Employees
WHERE Department = 'IT';
The highest salary in the IT department is 60000.
MIN() with GROUP BY
MIN() can be used with GROUP BY to find the minimum value for every group.
SELECT Department,
MIN(Salary) AS MinimumSalary
FROM Employees
GROUP BY Department;
Output
| Department | MinimumSalary |
|---|---|
| HR | 40000 |
| IT | 50000 |
| Sales | 35000 |
MAX() with GROUP BY
MAX() can be used with GROUP BY to find the maximum value for every group.
SELECT Department,
MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY Department;
Output
| Department | MaximumSalary |
|---|---|
| HR | 45000 |
| IT | 60000 |
| Sales | 35000 |
MIN() and MAX() with GROUP BY
We can use both functions together to see the lowest and highest value in every group.
SELECT
Department,
MIN(Salary) AS MinimumSalary,
MAX(Salary) AS MaximumSalary
FROM Employees
GROUP BY Department;
Output
| Department | MinimumSalary | MaximumSalary |
|---|---|---|
| HR | 40000 | 45000 |
| IT | 50000 | 60000 |
| Sales | 35000 | 35000 |
MIN(), MAX() and NULL Values
MIN() and MAX() ignore NULL values when calculating the minimum or maximum value.
Salary
------
50000
NULL
60000
40000
MIN() returns 40000 and MAX() returns 60000.
Real-World Uses
- Find the lowest employee salary.
- Find the highest employee salary.
- Find the minimum product price.
- Find the maximum product price.
- Find the lowest student marks.
- Find the highest student marks.
MIN() vs MAX()
| MIN() | MAX() |
|---|---|
| Returns the smallest value. | Returns the largest value. |
| Used to find minimum values. | Used to find maximum values. |
| Ignores NULL values. | Ignores NULL values. |
Important Points
- MIN() returns the smallest value.
- MAX() returns the largest value.
- Both are aggregate functions.
- Both can be used with WHERE.
- Both can be used with GROUP BY.
- Both ignore NULL values.
Summary
MIN() is used to find the smallest value and MAX() is used to find the largest value. Both functions are useful for analyzing numeric data and can be combined with WHERE and GROUP BY for more specific results.