SQL - MIN() - MAX() function

The MIN() and MAX() functions in SQL are aggregate functions. They are used to compare values in a set and, retrieve the maximum and minimum values respectively.

An aggregate function is a mathematical computation that takes a range of values as input and yields a single value expression, representing the significance of the provided data.

MAX() and MIN() aggregate functions are generally used in two ways:

• As functions, they are used with the GROUP BY clause of the SELECT statement.
• As expressions, they are used with a subquery and HAVING clause of SELECT statement.

The SQL MAX() Function

TheMAX()function compares the values in a column and returns the largest value among them.

Syntax

Following is the syntax of SQL MAX() function −

MAX(column_name);

Example

In the following example, we are running a query for MAX() function on a table named CUSTOMERS. The objective is to retrieve the maximum salary value from this table. First of all, let us create the CUSTOMERS table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

The CUSTOMERS table will be created as −

IDNAMEAGEADDRESSSALARY1Ramesh32Ahmedabad2000.002Khilan25Delhi1500.003Kaushik23Kota2000.004Chaitali25Mumbai6500.005Hardik27Bhopal8500.006Komal22Hyderabad4500.007Muffy24Indore10000.00

Here, we are comparing the salaries of CUSTOMERS and retrieving the maximum salary using the following query −

SELECT MAX(SALARY) FROM CUSTOMERS;

When the above query is executed, the result is displayed as −

MAX(SALARY)10000.0000

HAVING with MAX() Function

In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MAX() function along with HAVING clause.

SELECT ID, NAME, SALARY 
FROM CUSTOMERS 
GROUP BY NAME, ID 
HAVING MAX(SALARY) < 8000;

When the above query is executed, we get the details of the employees whose maximum salary is less than 8000 −

IDNAMESALARY1Ramesh2000.002Khilan1500.003Kaushik2000.004Chaitali6500.006Komal4500.00

MAX() Function in Subqueries

In the following example, we are using the MAX() function in a subquery to retrieve the record with maximum salary, from the CUSTOMERS table.

SELECT * FROM CUSTOMERS 
WHERE SALARY = (SELECT MAX(SALARY) FROM CUSTOMERS);

When we execute the above query, we will get the following result −

IDNAMEAGEADDRESSSALARY7Muffy24Indore10000.00

MAX() Function with Strings

This query retrieves the maximum value (alphabetically) among the names of customers in the CUSTOMERS table using the MAX() function −

SELECT MAX(NAME) AS max_name FROM CUSTOMERS;

Following is the result of the above query −

max_nameRamesh

Aliases with MAX() Function

In the following example, we use the MAX() function to retrieve the record containing maximum age from the CUSTOMERS table. We are displaying the results as a new column with the alias “max_age”.

SELECT MAX(age) AS 'max_age' FROM CUSTOMERS;

Following is the output of the above query −

max_age32

The SQL MIN() Function

TheMIN()function compares values in a column and returns the smallest value among them.

Syntax

Following is the syntax of SQL MIN() function −

MIN(column_name);

Example

In this example, we are comparing values in the SALARY column of CUSTOMERS table and displaying the minimum salary using the following query −

SELECT MIN(SALARY) FROM CUSTOMERS;

When the above query is executed, the result is displayed as −

MIN(SALARY)1500.0000

HAVING with MIN() Function

In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MIN() function along with HAVING clause.

SELECT ID, NAME, SALARY 
FROM CUSTOMERS 
GROUP BY NAME, ID 
HAVING MIN(SALARY) > 5000;

When the above query is executed, we get the details of the maximum salary for employees whose minimum salary is more than 5000, as we can see in the table that follows −

IDNAMEMAX_Salary4Chaitali6500.00005Hardik8500.00007Muffy10000.0000

MIN() Function in Subqueries

In the following example, we are using the MIN() function in a subquery to retrieve the record with minimum salary, from the CUSTOMERS table.

SELECT * FROM CUSTOMERS 
WHERE SALARY = (SELECT MIN(SALARY) FROM CUSTOMERS);

When we execute the above query, we will get the following result −

IDNAMEAGEADDRESSSALARY2Khilan25Delhi1500.00

MIN() Function with Strings

Following is the query to retrieve the minimum value (alphabetically) among the names of customers in the CUSTOMERS table using the MIN() function −

SELECT MIN(NAME) AS min_first_name FROM CUSTOMERS;

Following is the result of the above query −

min_first_nameChaitali

Aliases with MIN() Function

Following is the SQL query that will fetch the minimum age from the CUSTOMERS table using the MIN() function −

SELECT MIN(age) AS 'min_age' FROM CUSTOMERS;

When we execute the above query, the minimum value in the age field is displayed as shown below.

min_age22