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:

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:

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)

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)

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.