Jazz Jazz - 2 months ago 8
MySQL Question

Find the name of the customer who paid the most for a single order

This is my relational schema

enter image description here

From all the sales orders, I need to find the one with the highest total spent by a customer and to find the name of that customer.

I really don't know how to come up with a solution to this.

Finding the order with the highest total is not difficult:

SELECT MAX(total)
FROM sales_order


But I don't know how to go ahead. What I've been trying to do is

To come up with the order with the highest total -> find the
customer_id
from it -> Write down a script so that
sales_order.customer_id = customer_customer_id
-> show the
name
of the customer.

But I'm having trouble going from the 1st step to the 2nd one. The only thing I've been able to do is to get the highest amount of money spent by each customer in a single order:

SELECT MAX(total), customer_id
FROM sales_order
GROUP BY customer_id


Which is not what i'm looking for.

Thanks!!

Answer

I would probably go about this with a subquery in my where clause like so:

SELECT c.Name
FROM sales_order o
INNER JOIN Customer c ON c.Customer_ID = o.Customer_ID
WHERE Total = (SELECT MAX(Total) FROM sales_order)