SQL - MIN() - MAX() function

Table of content

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

The MAX() 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,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
```

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

```INSERT INTO CUSTOMERS VALUES
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);
```

The CUSTOMERS table will be created as −

2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.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 −

ID NAME SALARY
1 Ramesh 2000.00
2 Khilan 1500.00
3 Kaushik 2000.00
4 Chaitali 6500.00
6 Komal 4500.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 −

7 Muffy 24 Indore 10000.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_name
Ramesh

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_age
32

The SQL MIN() Function

The MIN() 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 −

ID NAME MAX_Salary
4 Chaitali 6500.0000
5 Hardik 8500.0000
7 Muffy 10000.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 −

2 Khilan 25 Delhi 1500.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_name
Chaitali

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_age
22