MySQL - MAX() Function

MySQL MAX() function is a type of aggregate function. It is used to retrieve the maximum value among the values in a particular column/table.

This function is mostly used in cases where a user needs to find the patterns in data stored. For instance, consider a sales company. The organization maintains a database to track their sales. In order to find their highest sold days, the MAX() function can be applied on a database table.

Syntax

Following is the syntax of MySQL MAX() function −

MAX(expr);

Parameters

This method accepts a parameter. The same is described below −

  • expr: This can be an expression or a particular column for which we want to find the maximum value.

Return value

This function returns the highest (maximum) value from the specified column in the specified table.

Example

In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE statement as follows −

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

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 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 );

To verify whether the records are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

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

Now, we are using the MySQL MAX() function to calculate the maximum salary among all the customers in the table −

SELECT MAX(SALARY) from CUSTOMERS;

Output

This will produce the following result −

MAX(SALARY)
10000.00

Example

A NULL value will be displayed as an output, if the result-set obtained from the MAX() function contains no rows.

In the below query, we are retrieving the maximum SALARY of a student named 'Raj'. But the table holds no records of 'Raj'. In this case, a NULL value will be returned.

SELECT MAX(SALARY) FROM CUSTOMERS 
WHERE NAME = 'Raj';

Output

This will return NULL as output −

MAX(SALARY)
NULL

Example

We can also use the MySQL MAX() function in subqueries to calculate the maximum salary of customers.

The following query retrieves name of the customer whose 'SALARY' is highest −

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

Output

Muffy is the customer who has the highest salary among all customers −

ID NAME AGE ADDRESS SALARY
7 Muffy 24 Indore 10000.00
Advertisements