Oracle MAX

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

Oracle MAX() function syntax

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

The following illustrates the syntax of MAX() function:

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

Similar to the MIN() function, the DISTINCT and ALL clauses are irrelevant to the MAX() function. The Oracle MAX() function also ignores NULL values.

Oracle MAX() function examples

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

A) Simple Oracle MAX() function example

The following example returns the list price of the most expensive (maximum) product:

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

B) Oracle MAX() in subquery

To get the most expensive product information, you use the following query:

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)
Oracle MAX - the most expensive product

In this example, the subquery returns the highest list price of all products. And the outer query selects the products whose list price is equal to the highest list price.

C) Oracle MAX() with GROUP BY clause

The following statement returns 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)
Oracle MAX - highest list price by category

In this example, 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 result set more readable.

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)
Oracle MAX - highest list price by category name

D) 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 the 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)
Oracle MAX - with HAVING example

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

In this tutorial, you have learned how to use the Oracle MAX() function to return the maximum value from a set of values.

Was this tutorial helpful?