How to find MAX over SUMs MySQL

Question

Consider the following scenario :

an Item has a Price

a Order contains Items

a Delivery contains Orders

I want to query for each delivery, the order with the highest price, where a price of an order is the summation of prices of the contained items.

A simple sufficient schema would look like this :

Delivery

d_id

Order

o_id

Item

i_id,price

ItemsInOrder

o_id,i_id

OrdersInDelivery

d_id,o_id

I am stuck in the point of having the summation results, needing to get the max order per delivery :

SELECT d_id,o_id,SUM(price)
from ItemsInOrder
natural join OrdersInDelivery
natural join Item
group by d_id,o_id

How should i go from here to get that each d_id, would appear once and aside the o_id with the maximal price summation?

Answer 1

Thanks for all your answers, but none of them was what i was looking for. I finally found what i was looking for and the approach i choose is as follows :

SELECT d_id,o_id,sum_price
FROM (
    SELECT d_id,o_id,SUM(price) as sum_price
    from ItemsInOrder
    natural join OrdersInDelivery
    natural join Item
    group by d_id,o_id
    order by d_id,sum_price desc
) as sums
GROUP BY d_id