MAX

The MAX function computes the maximum based on a group of values. For the examples that follow, please reference the orders table within the order entry (oe) schema at livesql.oracle.com. The contents of this table can also be found below. This table contains one row for each order placed by a customer.

COMPUTE THE MAXIMUM ORDER TOTAL ACROSS ALL ORDERS

SELECT
    MAX(oe.orders.order_total) AS maximum_order_total
FROM
    oe.orders;

In the example code above, note that a GROUP BY clause is not present because the code returns a single row representing the maximum order total value across all orders (295892).

COMPUTE THE MAXIMUM ORDER TOTAL FOR EACH CUSTOMER

SELECT
    oe.orders.customer_id,
    MAX(oe.orders.order_total) AS maximum_order_total
FROM
    oe.orders
GROUP BY
    oe.orders.customer_id;

When an aggregate must be computed across and for each set of grouped rows, a GROUP BY clause is necessary. Here, the rows have been grouped by customer ID and for each of those sets of grouped rows, the maximum order total is computed.

COMPUTE THE MAXIMUM ORDER TOTAL FOR THE TOP CUSTOMER(S) IN TERMS OF ORDERS PLACED

SELECT
    oe.orders.customer_id,
    MAX(oe.orders.order_total) AS maximum_order_total
FROM
    oe.orders
GROUP BY
    oe.orders.customer_id
HAVING
    COUNT(*) = (
		SELECT
			MAX(order_counts.order_count)
		FROM (
			SELECT
				COUNT(*) AS order_count
			FROM
				oe.orders
			GROUP BY
				oe.orders.customer_id
		) order_counts
	);

The solution here is largely the same as computing the maximum order total for each customer; however, the addition of the HAVING clause permits the maximum order total to be computed only across the set(s) of grouped rows corresponding to the top customer(s) in terms of number of orders placed. Problems such as this one are often easier to tackle when the problem is broken down into two or more subproblems or steps. This problem can be broken down into three steps.

The first step is to write a query that returns the maximum order total for each customer. The second step is to write a query that returns a single value representing the largest number of orders placed by any customer. Luckily, neither of these problems are terribly difficult when solved individually. After solving for the two queries, it’s a simple as placing the query from step two in the HAVING clause as a subquery, as seen above. The most difficult part is realizing that this seemingly complex problem is a couple of simple problems that, when solved individually, the complexity disappears. Break-down problems into several subproblems or steps when you can.

RETRIEVE ORDER DETAILS FOR THE MOST RECENT ORDER PLACED

SELECT
    oe.orders.order_id,
    oe.orders.order_date,
    oe.orders.customer_id,
    oe.orders.order_total
FROM
    oe.orders
WHERE
    oe.orders.order_date = (
		SELECT
			MAX(oe.orders.order_date)
		FROM
			oe.orders
	);

The solution above utilizes a subquery in the WHERE clause. The subquery returns a single value, the maximum order date value within the orders table. This value is then compared to each row’s order date value in the outer query and if the values match, the corresponding row is returned. If multiple orders were placed at the same time (including fractional seconds), then more than one row would be returned, one for each order placed at that time.

RETRIEVE ORDER DETAILS FOR EACH CUSTOMER’S LARGEST ORDER(S) PLACED IN TERMS OF AMOUNT SPENT

-- Using a JOIN to a derived table to solve.
SELECT
    oe.orders.order_id,
    oe.orders.order_date,
    oe.orders.customer_id,
    oe.orders.order_total
FROM
    oe.orders
    INNER JOIN (
    SELECT
    	oe.orders.customer_id,
    	MAX(oe.orders.order_total) AS order_total
    FROM
    	oe.orders
    GROUP BY
    	oe.orders.customer_id
    ) largest_order
        ON oe.orders.customer_id = largest_order.customer_id
        AND oe.orders.order_total = largest_order.order_total;

-- Using common table expression and DENSE_RANK() to solve.
WITH largest_order AS (
    SELECT
    	oe.orders.order_id,
    	oe.orders.order_date,
    	oe.orders.customer_id,
    	oe.orders.order_total,
    	DENSE_RANK() OVER (
    		PARTITION BY oe.orders.customer_id
    		ORDER BY oe.orders.order_total DESC
    	) AS order_rank
    FROM
    	oe.orders
)
SELECT
    largest_order.order_id,
    largest_order.order_date,
    largest_order.customer_id,
    largest_order.order_total
FROM
    largest_order
WHERE
    largest_order.order_rank = 1;

Retrieving the order details for each customer’s orders is straightforward. The difficulty in this problem is having to filter the orders to only the customer’s largest order(s) in terms of amount spent (i.e., order total). Like the problem above, this one can be broken down into a couple of steps.

First, we need to write a query returning the customer ID and corresponding order total associated with each customer’s largest order(s). We’ll use this information to filter the orders table to the largest order(s) placed by each customer by joining it to an outer query containing the details of the associated and matched order. You’ll notice we have two conditions in our ON clause. First, the customer ID in the outer-query and subquery must match and, second, the order total must match. The latter of the two is less obvious, but critical to our solution because we only want the largest order placed by each customer. In the absence of the second condition filtering on order total, the same result-set as retrieving all orders from the orders table would be returned.

A common table expression (CTE), containing each the details of each customer’s orders and the rank associated with that order when computed across only the respective customer’s orders, is used in the second solution. The SELECT statement that follows the CTE filters the temporary result set to only those records associated with each customers largest order(s) in terms of amount spent (i.e., largest_order.rank = 1).

Knowledge Check
Need more practice or simply wish to test your understanding? Give a few of the practice problems I’ve provided in the SQL Practice Problems section a try.