Which SQL Keyword is Used to Retrieve a Maximum Value?

The SQL MAX() function is used to find the maximum value of a certain column or an expression in a table. It is one of the aggregate functions used in SQL.

Aggregate functions are those functions that perform calculations on a group of values and return a single value as a result. MAX() function is applied to a group of data to return the maximum value from them as a result.

NOTE: The MAX function ignores NULL values.

Syntax of the MAX() Keyword in SQL:

CASE 1:

The MAX() function returns the largest value present in the selected column of the table.

Let's understand this with the help of an example:

Firstly, let's suppose you have a student_details table, having ROLL_NO, FIRST_NAME, LAST_NAME, BRANCH and MARKS as the fields, and some data has been stored in these fields.

Let's see how our table's data looks by using the SELECT command.

NOTE: * represents all the columns, which means we are selecting or retrieving all the data from the given table.

OUTPUT:

ROLL_NO	FIRST_NAME	LAST_NAME	MARKS	BRANCH
1	Rita	Goyal	67	CSE
2	Kanika	Mittal	91	IT
3	Jyoti	Sharma	21	CSE
4	Reshma	Kumari	100	IT

If you want to find the maximum marks that any student in this table achieves, the following query can be used:

OUTPUT:

MAX(MARKS)
100

Output is a single number representing the highest marks in the student_details table.

CASE 2:

You can also provide some conditions using the WHERE clause in the MAX() function query that must be satisfied. Based on those conditions, you can calculate the maximum value from the records that fulfil the criteria.

Syntax:

Let's say you want to show the maximum marks received by students, along with their complete names. Use the following SQL query to achieve this task.

Here, we are running a subquery to return the maximum marks in the student_details table. Then, we check for the record having the marks same as the maximum marks calculated by the subquery. As soon as we get the record with the highest marks, our condition is satisfied and we will display the students' complete names and marks.

OUTPUT:

FIRST_NAME	LAST_NAME	MAX(MARKS)
Reshma	Kumari	100

Here, the output contains a single student having the highest mark of 100.

CASE 3:

You can also use the GROUP BY clause along with the SQL MAX() function to find the maximum value of a column over each group.

Here, the expression specifies the column(s) name, which needs to be grouped for the query.

Suppose you want to filter out some data from a particular group according to some condition. Then you can use the HAVING clause specifying the condition that needs to be fulfilled.

Let's see the syntax for adding HAVING clause along with the GROUP BY clause:

Here, the HAVING clause will filter out the groups according to the condition specified.

Let's understand this with the help of an example:

Suppose you want to find the maximum marks achieved by the student who belongs to the CSE branch.

For this query, you have to write the below statement in SQL:

Here, we are using the GROUP BY clause, which is used for grouping the rows with the same values. We are grouping based on the BRANCH field in this case. So, the CSE branch rows will be grouped together and IT branch rows will be grouped together. Now, we are applying the HAVING clause which filters out the groups formed by the GROUP BY clause.

NOTE: We are not using the WHERE clause here because the HAVING clause is applied to each group of the grouped table(this is our case here), and the WHERE clause is applied to individual rows.

The GROUP BY clause will group rows 1 and 3 together and rows 2 and 4 together. The HAVING clause will filter out the group having rows 1 and 3 together as they satisfy the condition of having a CSE branch.

And, then the MAX function will run on this group to get the maximum marks of the students having CSE as their branch.

OUTPUT:

MAX(MARKS)
67

Here, the output is 67 because only students with ROLL_NO 1 and 3 are in the CSE branch, and the maximum of 67 and 21 is 67.

CASE 4:

In this example, we have to deal with NULL values also. Firstly, let's see how our table looks with NULL values.

OUTPUT:

ROLL_NO	FIRST_NAME	LAST_NAME	MARKS	BRANCH
1	Rita	Goyal	67	CSE
2	Kanika	Mittal	NULL	IT
3	Jyoti	Sharma	21	CSE
4	Reshma	Kumari	NULL	IT

As we can see from the above output table some entries are NULL in the MARKS column.

Now, let's find the maximum marks in our student_details table.

OUTPUT:

MAX(MARKS)
67

Output is a single number representing the highest marks in the student_details table, ignoring the fields having null values.

How Do We Find the Maximum Value of Multiple Rows in SQL?

Suppose you want to find the rows which store the largest numeric value in a given column.

For that purpose, let's modify our table and update the marks of any student equal to the maximum marks.

Let's see how our table's data looks by making use of the SELECT command.

OUTPUT:

ROLL_NO	FIRST_NAME	LAST_NAME	MARKS	BRANCH
1	Rita	Goyal	67	CSE
2	Kanika	Mittal	91	IT
3	Jyoti	Sharma	100	CSE
4	Reshma	Kumari	100	IT

Now, we will again run the query to find the details of the students who have scored maximum marks.

OUTPUT:

FIRST_NAME	LAST_NAME	Marks
Jyoti	Sharma	100
Reshma	Kumari	100

As we can see, two students have marks equal to the maximum, i.e., 100.

Learn more about keyword in sql

Conclusion

MAX function in SQL allows you to find the maximum value in a set of values.
The MAX function in SQL ignores NULL values.
MAX function is used in conjunction with the GROUP BY clause to find the maximum value per group.
MAX function is used with the HAVING clause to add the condition to the groups returned by the GROUP BY clause.
MAX function can also be used with other functions like the ORDER BY clause, in which we have to place the MAX function in the ORDER BY clause to sort the result set based on the result of the MAX function.