Db2 MAX

Summary: in this tutorial, you will learn how to use Db2 MAX() function to find the highest value in a set of values.

Introduction to Db2 MAX() function

The MAX() function is an aggregate function that returns the maximum value in a set of value. Here is the syntax of the MAX() function:

MAX(expression)
Code language: SQL (Structured Query Language) (sql)

The MAX() function returns the highest value in a set. If the set is empty, the MAX() function returns NULL.

Note that the MAX() function ignores NULL values.

Db2 MAX() function examples

We’ll use books table to demonstrate the MAX() function.

1) Using Db2 MAX() function to get the highest value example

This example uses the MAX() function to return the highest book rating:

SELECT MAX(rating) max_rating FROM books;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

MAX_RATING ---------- 5.00
Code language: SQL (Structured Query Language) (sql)

To find the detailed information of the book that has the highest rating, you use the following query:

SELECT title, isbn, rating FROM books WHERE rating = ( SELECT MAX(rating) max_rating FROM books );
Code language: SQL (Structured Query Language) (sql)

Here is the book with the highest rating:

db2 max function - find highest value example

2) Using Db2 MAX() function with GROUP BY clause example

We often use the MAX() function with the GROUP BY clause to find the maximum value in each group.

First, the GROUP BY clause divides values into groups. Then, the MAX() returns the highest value for each.

The following example returns the highest book ratings for each publisher.

SELECT p.name publisher, max(b.rating) max_rating FROM books b INNER JOIN publishers p ON p.publisher_id = b.publisher_id GROUP BY p.name ORDER BY max_rating DESC;
Code language: SQL (Structured Query Language) (sql)

Here is the list of publishers with their highest book ratings:

db2 max function with group by clause

3) Using Db2 MAX() function with HAVING clause example

To filter publishers whose highest ratings of books are greater than 4.7, you use the MAX() function in the HAVING clause as shown in the following example:

SELECT p.name publisher, MAX(b.rating) max_rating FROM books b INNER JOIN publishers p ON p.publisher_id = b.publisher_id GROUP BY p.name HAVING MAX(b.rating) > 4.7 ORDER BY max_rating DESC;
Code language: SQL (Structured Query Language) (sql)
db2 max function with having clause

Note that Db2 evaluates the HAVING clause before the SELECT clause, therefore, you have to use the MAX() function in the HAVING clause instead of the column alias of the MAX(b.rating) expression which is max_rating.

4) Using Db2 MAX() function with AVG() function example

To find the highest rating of the average book ratings by publishers, you use the following statement:

WITH cte AS( SELECT p.name publisher, AVG(b.rating) avg_rating FROM books b INNER JOIN publishers p ON p.publisher_id = b.publisher_id GROUP BY p.name ) SELECT MAX(avg_rating) FROM cte;
Code language: SQL (Structured Query Language) (sql)
db2 max function find max of average

In this example:

  • First, the common table expression cte returns the average book ratings by publishers.
  • Then, the MAX() function returns the highest rating from the average ratings.

In this tutorial, you have learned how to use the Db2 MAX() function to find the highest value in a set of values.

Was this tutorial helpful ?