SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server - MIN() & MAX() Functions



The SQL Server (Transact-SQL) MIN() function returns the minimum value of an expression or specified column of a table. Similarly, the SQL Server (Transact-SQL) MAX() function returns the maximum value of an expression or specified column of a table.

Syntax

The syntax for using MIN() and MAX() functions in SQL Server (Transact-SQL) are given below:

/* Using MIN() Function */
SELECT MIN(column_name) 
FROM table_name
WHERE condition(s);

/* Using MAX() Function */
SELECT MAX(column_name) 
FROM table_name
WHERE condition(s);

The SQL Server GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the MIN() / MAX() function. See the syntax below:

/* Using GROUP BY with MAX() Function */
SELECT column1, column2, ...
       MIN(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

/* Using GROUP BY with MAX() Function */
SELECT column1, column2, ...
       MAX(column_name) 
FROM table_name
WHERE condition(s)
GROUP BY column1, column2, ...;

Example - With Single Column

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • To fetch minimum Salary of the employees present in the Employee table, the query will be:

    SELECT MIN(Salary) AS MinSalary 
    FROM Employee;
    

    This will produce the result as shown below:

    MinSalary
    2750
  • To fetch maximum Salary of the employees present in the Employee table, the following query can be used:

    SELECT MAX(Salary) AS MaxSalary 
    FROM Employee;
    

    This result of the following code will be:

    MaxSalary
    3100


Example - Using GROUP BY

Consider the above discussed database named Employee which contains the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • To fetch the minimum Salary of the employees group by their age, where age is greater than 27, the following query can be used:

    SELECT Age, MIN(Salary) AS MinSalary 
    FROM Employee
    WHERE Age > 27
    GROUP BY Age;
    

    This result of the above code will be:

    AgeMinSalary
    282800
    303100
  • To fetch the maximum Salary of the employees group by their age, where age is greater than 27, the query will be:

    SELECT Age, MAX(Salary) AS MaxSalary 
    FROM Employee
    WHERE Age > 27
    GROUP BY Age;
    

    This result of the above code will be:

    AgeMaxSalary
    283000
    303100

5