MAX Function in SQL

The MAX is an aggregate function in SQL which returns the maximum or largest value from the specified column of the table.

Syntax of MAX Function

In the Structured Query Language, we use the MAX function with the columns of the table as shown in the following block:

In this syntax, we have to define the name and column of that table on which we want to perform the MAX function.

Example of MAX function

Here, we will create the new table through which we will perform the MAX function with the columns of the table:

The following shows the syntax to create the new table in SQL:

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:

The following SELECT statement displays the inserted records of the above Product_Details table:

Query 1: The following SELECT query uses the MAX function with the Product_Quantity column of the above Product_Details table:

This query shows the largest value of product quantity column.

Output:

Query 2: The following SELECT query uses the MAX function with the Selling_Price column of the above Product_Details table:

This query returns the maximum value of selling price products.

Output:

Query 3: The following SELECT query uses the MAX function with the Product_Rating column of the above Product_Details table:

This query returns the largest values of Product_rating columns.

Output:

Query 4: The following SELECT query uses the MAX function with the addition of two columns of the above Product_Details table:

This query returns the largest values from the addition of purchasing and selling price.

Output:

MAX Function with WHERE clause

We can also use the WHERE clause with the MAX function which returns the largest value from the filtere rows.

The syntax for using the MAX function with the WHERE clause is as follows:

Query 1: The following SELECT query uses the MAX function with WHERE clause in the above Product_Details table:

This query returns the largest quantity from those products whose product id is greater than 200.

Output:

Query 2: The following SELECT query uses the MAX function with WHERE clause in the above Product_Details table:

This query returns the largest purchasing price from those products which are release on 2022-01-28.

Output:

MAX Function with GROUP BY clause

We can also use the GROUP BY clause with the MAX function which returns the maximum value from the same group.

The syntax for using the MAX function with the GROUP BY clause is as follows:

Query 1: The following SELECT query uses the MAX function with the GROUP BY clause in the above Product_Details table:

This query returns the maximum value from each specified group.

Output: