Oracle MAX Function

Summary: in this tutorial, you will learn how to use the Oracle MAX() function to return the maximum value from a set of values.

Introduction to Oracle MAX() function #

The Oracle MAX() function is an aggregate function that returns the maximum value of a set.

Here’s the syntax of MAX() function:

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

The Oracle MAX() function also ignores NULL.

Like the MIN() function, the DISTINCT and ALL clauses are irrelevant to the MAX() function.

Oracle MAX() function examples #

We’ll use the products and product_categories tables in the sample database for the demonstration.

Basic Oracle MAX() function example #

The following example uses the MAX function to returns the highest list price from the products table:

SELECT
  MAX(list_price)
FROM
  products;Code language: SQL (Structured Query Language) (sql)

Try it

Using Oracle MAX() function in subquery #

The following example uses the MAX function in a subquery to get the highest price:

SELECT
    product_id,
    product_name,
    list_price
FROM
    products
WHERE
    list_price =(
        SELECT
            MAX( list_price )
        FROM
            products
    );
Code language: SQL (Structured Query Language) (sql)

Try it

In this example:

• First, the subquery returns the highest list price of all products.
• Second, the outer query retrieves products whose list price is equal to the highest price.

Using Oracle MAX() with GROUP BY clause #

The following statement uses the MAX function with a GROUP BY clause to return the list price of the most expensive product by product category:

SELECT
  category_id,
  MAX(list_price)
FROM
  products
GROUP BY
  category_id
ORDER BY
  category_id;Code language: SQL (Structured Query Language) (sql)

Try it

In this example:

• First, the GROUP BY clause groups the products by product category (category_id) into subgroups.
• Then, the MAX() function returns the highest price of products for each group.

Instead of using the category_id, you can use the product category name to make the output more meaningful.

To do this, you join the products table with the product_categories table as shown below:

SELECT
  category_name,
  MAX(list_price)
FROM
  products
  INNER JOIN product_categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  category_name;Code language: SQL (Structured Query Language) (sql)

Try it

Using Oracle MAX() function with HAVING clause #

The following query retrieves the product category and the list prices of the most expensive product per each product category. In addition, it returns only the product category whose highest list price is between 3000 and 5000:

SELECT
    category_name,
    MAX( list_price )
FROM
    products
INNER JOIN product_categories
        USING(category_id)
GROUP BY
    category_name
HAVING
    MAX( list_price ) BETWEEN 3000 AND 6000
ORDER BY
    category_name;
Code language: SQL (Structured Query Language) (sql)

Try it

In this example, the HAVING clause specifies the condition for filtering the groups.

Summary #

• Use the MAX() function to return the maximum value from a set of values.
• Use the MAX function with the GROUP BY clause to return the maximum value for each group.