Jazz Jazz - 1 year ago 78
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:

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
from it -> Write down a script so that
sales_order.customer_id = customer_customer_id
-> show the
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.


Answer Source

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

FROM sales_order o
INNER JOIN Customer c ON c.Customer_ID = o.Customer_ID
WHERE Total = (SELECT MAX(Total) FROM sales_order)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download