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:
The MAX() function compares the values in a column and returns the largest value among them.
Following is the syntax of SQL MAX() function −
MAX(column_name);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 −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 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 |
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.0000In 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 |
SELECT * FROM CUSTOMERS
WHERE SALARY = (SELECT MAX(SALARY) FROM CUSTOMERS);
Result −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 7 | Muffy | 24 | Indore | 10000.00 |
SELECT MAX(NAME) AS max_name FROM CUSTOMERS;Output:
max_name
RameshSELECT MAX(age) AS 'max_age' FROM CUSTOMERS;Output:
max_age
32The MIN() function compares values in a column and returns the smallest value among them.
MIN(column_name);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;Output:
MIN(SALARY)
1500.0000
SELECT ID, NAME, SALARY
FROM CUSTOMERS
GROUP BY NAME, ID
HAVING MIN(SALARY) > 5000;
Result −
| ID | NAME | MAX_Salary |
|---|---|---|
| 4 | Chaitali | 6500.0000 |
| 5 | Hardik | 8500.0000 |
| 7 | Muffy | 10000.0000 |
SELECT * FROM CUSTOMERS
WHERE SALARY = (SELECT MIN(SALARY) FROM CUSTOMERS);
Result −
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 2 | Khilan | 25 | Delhi | 1500.00 |
SELECT MIN(NAME) AS min_first_name FROM CUSTOMERS;Output:
min_first_name
ChaitaliSELECT MIN(age) AS 'min_age' FROM CUSTOMERS;Output:
min_age
22