SQL - MIN() & MAX() function

In SQL, MIN() and MAX() are aggregate functions. The MIN() function returns the smallest value of the selected column, while the MAX() function returns the largest value of the selected column.

Aggregate functions are often used in databases, spreadsheets, and statistical software packages which are now common in workplaces.

A mathematical calculation that takes a range of values as input and results in a single value expression, that represents the significance of the given data is known as an aggregate function.

Syntax

Following is the syntax of the MIN() function

SELECT MIN(column_name) FROM table_name WHERE conditions;

Following is the syntax of the MAX() function

SELECT MAX(column_name) FROM table_name WHERE conditions;

As we can see above, these functions are used with WHERE clauses to select the maximum value from the filtered records.

Example

In the following example, we are using the Customers table to run the query for the MIN() and MAX() functions. These functions are expected to display the minimum and maximum salary from this table respectively.

Assume we have created a table named Customers, which contains the personal details of customers including their name, age, address and salary etc., 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 (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

The table will be created as −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the query to display the min and max salaries of the customers −

SELECT MIN(SALARY), MAX(SALARY) FROM customers;

Output

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

+------------------+------------------+
| (No column name) | (No column name) |
+------------------+------------------+
|   1500.0000      |  10000.0000      |
+------------------+------------------+

Apases with MAX() and MIN()

Sometimes, field names in a database table are difficult to read and understand. Hence, there arises a need to make the data in these fields more understandable. This is why, the concept of apases is introduced in SQL.

Apases are used to assign a temporary custom name to the columns of a table using with the "AS" keyword. These custom names are aimed to be simpler to read and easy to access the fields while executing a query.

They can also be used with the MIN() and MAX() functions.

Syntax

Following is the syntax of MIN() function with the “as” keyword.

SELECT MIN(column_name) as apas_name FROM table_name;

Following is the syntax of MAX() function with the “as” keyword.

SELECT MAX(column_name) as apas_name FROM table_name;

Example

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;

Output

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

+------------------+
|     min_age      |
+------------------+
|        23        |
+------------------+

Example

In the following example, we use the MAX() function and write a query to retrieve data from the CUSTOMERS table that have a maximum age and also displaying the Apas name "max_age".

SELECT MAX(age) AS  max_age  FROM CUSTOMERS;

Output

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

+------------------+
|       max_age    |
+------------------+
|        35        |
+------------------+

MAX() and MIN() with strings

In addition to numerical values we can also use the MAX() and MIN() functions with string data types such as text.

Example

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

SELECT MIN(first_name) AS min_first_name FROM CUSTOMERS;

Following is the query to retrieve the maximum value among the names of customers in the customer table using the MAX() function −

SELECT MAX(first_name) AS max_first_name FROM CUSTOMERS;

Selecting Full Row With MIN Or MAX Value

If we want to display the entire row with the max or min value, we can use the nested "select" statement.

Syntax

Following is the syntax to select the entire row containing the minimum age.

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

Following is the syntax to select the entire row containing the maximum age.>

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

Example

In the following example, we use the MAX() function and write a query to retrieve rows from the CUSTOMERS table that have a maximum greater name.

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

Output

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

+----+--------+-----+-----------+-----------+
| ID | NAME   | AGE | ADDRESS   | SALARY    |
+----+--------+-----+-----------+-----------+
|  1 | Ramesh |  32 | Ahmedabad | 2000.0000 |
+----+--------+-----+-----------+-----------+
1 row in set (0.05 sec)

Using MIN() and MAX() in the HAVING CLAUSE

The use of min() and max() functions along with the having clause is helpful to filter the data based on the minimum and maximum values of a column.

Syntax

Following is the syntax of min and max with having clause −

SELECT column_name,
MAX(column_name)/MIN(column_name)
FROM table_name
GROUP BY column_name
HAVING MIN(column_name)/MAX(column_name) > value;

Example

In the following example, we are trying to fetch the ID, NAME, and SALARY of the customers using the MIN() function along with having clause.

SELECT ID, NAME,
MAX(SALARY) AS MAX_Salary
FROM customers
GROUP BY NAME, ID
HAVING MIN(SALARY) > 5000;

Output

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 | Chaitap |  6500.0000 |
|  5 | Hardik   |  8500.0000 |
|  7 | Muffy    | 10000.0000 |
+----+----------+------------+