MySQL MAX function

The MAX function is a type of aggregation function that determines the maximum value of the table data. This function works on numerical data type values. If the table displays zero value, then the row does not exist in the table column. The max function returns a maximum value of the single or multiple columns.

Syntax

The basic MAX () function syntax shows below.

MAX(function_expression)

MySQL MAX () function syntax with table shows below.

SELECT MAX(function_expression) FROM Table_Name WHERE condition;

Description

• The MAX() function returns a maximum number of the rows of the table.
• The "function_expression" represents the table value or column data for the "SUM" function's operation.
• The "WHERE" clause applies logical conditions on the MAX() function.

Prerequisite for MAX function

• Create the new database in the command line prompt interface.

mysql> create database mysqldb;

• Use the given database to create and operate tables.

mysql> use mysqldb;

• Create a new table with the columns and their data type.

mysql> create table product (  

    number int NOT NULL,

    product varchar(100) NOT NULL,    

    available int,  

    required int,  

    received_date date

    );  

• Insert the data in the table as per data type.

mysql> insert into product(number, product, available, required, received_date) 

VALUES    

(1, 'inkpen', 78, 45, '2021-07-01'),  

(2, 'ballpen', 100, 65, '2021-07-01'),  

(3, 'pencil', 78, 45, '2021-08-24'),  

(4, 'eraser', 50, 50, '2021-11-21'),  

(5, 'pen holder', 90, 50, '2021-07-01'),  

(6, 'books', 100, 65, '2021-10-13');

• You can refer to the table structure and information using the output query.

mysql> SELECT * FROM product;

Examples of the MAX function

1) Example: The following query shows the operation of the MAX function. This query is a basic sample of the "MAX" function. This function works on the numerical data type.

mysql> SELECT MAX(available) AS available_product  FROM product;

 Output

The above output shows you the maximum value of the "available" column. This output returns the largest value of the given data.

2) Example: The below example explain the MAX function with the date value. Execute the MAX function on the date column. This function converts a date into a number and operates to determine the largest value.

mysql> SELECT MAX(received_date ) AS date FROM product;

Output

The output returns the largest date of the product table. The MAX function displays the largest date in date format.

3) Example: The MAX function with the "DISTINCT" and the "WHERE" clauses example.

Execute the below query in which the MAX function works with the WHERE clause. This condition applies to the "available" column.

mysql> SELECT MAX(DISTINCT available) AS available_product  
FROM product 
WHERE available < 60;

 Output

4) Example: The MAX functions with multiple columns example and output.

This example works similarly to the single-column MAX function. Here, you can use multiple columns like a number, available, required, and date column of the table.

mysql> SELECT MAX(number) AS number, 
MAX(received_date) AS date, 
MAX(available) AS available_product, 
MAX(required) AS required_product
FROM product;

Output

The max function returns the maximum value of each column in the above image. This image shows you the largest value of the given table. The multiple rows data displays in a single row in the output table.

5) Example: The MAX functions with the "WHERE" clause and "AND" operator example.

The MAX function comes with a number, available, and receives columns of the table. This clause works on the "available" and "required" columns of the table. The AND logical condition comes between the "available" and "required" columns.

mysql> SELECT MAX(number) AS number, 
MAX(received_date) AS date, 
MAX(available) AS available_product, 
MAX(required) AS required_product
FROM product 
WHERE number < 4 AND required > 50;

Output

6) Example: The MAX functions with the "GROUP BY" clause example.

This MAX function example uses multiple columns of the table along with the GROUP BY clause. This grouping clause works on the available column of the table.

mysql> SELECT MAX(number) AS number, 
MAX(received_date) AS date, 
MAX(available) AS available_product, 
MAX(required) AS required_product
FROM product
GROUP BY available;

Output

This function displays the largest values of the table. This output returns four rows of the table data. The "GROUP BY" clause applies to the available column.

7) Example: The MAX functions with the "GROUP BY" and "HAVING" clauses.

Execute the MAX function query using multiple columns of the table. This function uses the ORDER BY clause with ascending order condition.

mysql> SELECT MAX(number) AS number, 
MAX(received_date) AS date, 
MAX(available) AS available_product, 
MAX(required) AS required_product
FROM product
GROUP BY available
HAVING available < 60;

Output

The MAX function returns the largest value of the name, date, available_product, and required_product columns. The "HAVING" clause applies less than 50 value conditions on the table.