Objective

Given two tables, customers and orders, write an SQL query to retrieve each customer's name along with the ID and total amount of their highest-value order. The results should be ordered alphabetically by the customer's name.

Additional information

• Each customer may have multiple orders.
• If a customer has multiple orders with the same highest total amount, return the order with the smallest order_id.

The customers table:

The orders table:

• Ensure that all customers with at least one order are included in the result.
• Order the final output by customer_name in ascending order.

SELECT
    c.customer_name,
    o.order_id,
    o.total_amount
FROM
    customers c
    JOIN orders o ON c.customer_id = o.customer_id
WHERE
    o.order_id = (
        SELECT order_id
        FROM orders o2
        WHERE o2.customer_id = c.customer_id
        ORDER BY total_amount DESC, order_id ASC
        LIMIT 1
    )
ORDER BY
    c.customer_name;

Explanation

Step 1: Join customers to their orders

FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

An inner join ensures only customers who have placed at least one order are included.

Step 2: Correlated subquery to find the best order

SELECT order_id
FROM orders o2
WHERE o2.customer_id = c.customer_id
ORDER BY total_amount DESC, order_id ASC
LIMIT 1

For each customer, the subquery sorts their orders by total_amount descending (highest first), then by order_id ascending (smallest first) to break ties. LIMIT 1 returns exactly one order_id. This handles the case where multiple orders share the same highest amount by picking the one with the smallest ID.

Step 3: Filter to keep only that order

WHERE o.order_id = (...)

The outer query matches only the single order returned by the subquery, guaranteeing one row per customer.

Step 4: Sort alphabetically

ORDER BY c.customer_name;

Why not use MAX(total_amount)?

A simpler approach using WHERE o.total_amount = (SELECT MAX(total_amount) ...) would fail when two orders have the same highest amount. It would return both rows instead of just the one with the smallest order_id.