MIN

The MIN function computes the minimum 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 MINIMUM ORDER TOTAL ACROSS ALL ORDERS

SELECT
    MIN(oe.orders.order_total) AS minimum_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 minimum order total value across all orders (48).

COMPUTE THE MINIMUM ORDER TOTAL FOR EACH CUSTOMER

SELECT
    oe.orders.customer_id,
    MIN(oe.orders.order_total) AS minimum_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 minimum order total is computed.

COMPUTE THE MINIMUM ORDER TOTAL FOR EACH CUSTOMER HAVING PLACED AT LEAST 5 ORDERS

SELECT
    oe.orders.customer_id,
    MIN(oe.orders.order_total) AS minimum_order_total
FROM
    oe.orders
GROUP BY
    oe.orders.customer_id
HAVING
    COUNT(*) >= 5;

The solution here is largely the same; however, the addition of the HAVING clause permits the minimum order total to be computed only across those sets of grouped rows made up of at least five rows. Effectively, this limits our customer ID listing and the corresponding minimum order total values to only those customers that have placed at least five orders. How do we know if this strategy involving the HAVING clause is correct? The orders table contains one row per order. It’s important to know your data.

RETRIEVE ORDER DETAILS FOR THE FIRST 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
			MIN(oe.orders.order_date)
		FROM
			oe.orders
	);

The solution above utilizes a subquery in the WHERE clause. The subquery returns a single value, the minimum 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 THE FIRST ORDER PLACED FOR EACH CUSTOMER

-- 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,
		MIN(oe.orders.order_date) AS order_date
	FROM
		oe.orders
	GROUP BY
		oe.orders.customer_id  
    ) first_order
        ON oe.orders.customer_id = first_order.customer_id
    	AND oe.orders.order_date = first_order.order_date;

-- Using a subquery in the WHERE clause to solve.
SELECT
    oe.orders.order_id,
    oe.orders.order_date,
    oe.orders.customer_id,
    oe.orders.order_total
FROM
    oe.orders
WHERE
    (oe.orders.customer_id, oe.orders.order_date) IN (
		SELECT
			oe.orders.customer_id,
			MIN(oe.orders.order_date)
		FROM
			oe.orders
		GROUP BY
			oe.orders.customer_id
	);

Retrieving the order details for each customer’s first order, based on the order date, simply requires us to build onto the previous solution where the details of the first order placed, regardless of customer, was retrieved; however, minor adjustments are necessary. First, we need to write a query returning the customer ID and corresponding order date associated with each customer’s first order. We’ll use this information to filter the orders table to the first order 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 date must match. The latter of the two is less obvious, but critical to our solution because we only want the first order placed by each customer. In the absence of the second condition filtering on order date, the same result-set as retrieving all orders from the orders table would be returned.

In the second solution, a subquery in the WHERE clause is used, and the tuples (customer ID, order date) returned by the outer query and subquery are compared. Comparisons resulting in a match are returned, resulting in the order details for each customer’s first order being retrieved. I’ve honestly never seen a solution in the real world take advantage of this strategy, so I’d caution on using it as it may be more difficult for others reading your code to read and understand.

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.