MySQL - Find min price using join

I have a table like so:

order_id INT product_id INT average_price DOUBLE other_price DOUBLE

There are multiple product_ids per order_id i.e. multiple products per order. I want to the product with the minimum average price per order and out of these products those for which other_price >= average_price.

I've tried this:

SELECT a.order_id, a.product_id, a.average_price, a.other_price
FROM
    (S开发者_高级运维ELECT order_id, product_id, min(average_price) as average_price
     FROM orders
     GROUP BY order_id
    ) as min_priced_product_per_order
JOIN orders AS a ON a.order_id = min_priced_product_per_order.order_id and min_priced_product_per_order.product_id = min_priced_product_per_order.product_id 
WHERE a.other_price >= a.average_price

But the results are not what I expected, I am not convinced that I am getting the product_id with the minimum average price for each order (I've confirmed by inspecting the DB).

Can you suggest a better query?

Cheers.

Pete

The key to your problem is that product_id is just sitting loose without a grouping nor an aggregate function (This query will not fly in most RDMS's).

(SELECT order_id, product_id, min(average_price) as average_price
     FROM orders
     GROUP BY order_id
) as min_priced_product_per_order

This a great extension MySQL cooked up to avoid certain joins that required sub-queries that in the past it did not support. product_id is being chosen arbitrarily i.e. whatever row it found first for no particular reason. This surely will not (well it could by accident) return the results you would like if you are looking for a certain product_id. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

You can always write these kinds of queries by dropping the offending id (product_id) out the aggregate in the inner query and then join against your aggregate value in this case min(average_price) in the outer query to find the id.

(SELECT min_order.order_id, product_id, min_order.average_price
FROM
(SELECT order_id, min(average_price) as average_price
     FROM orders
     GROUP BY order_id
 ) AS min_order
 JOIN orders on 
    min_order.product_id = order.product_id
    AND min_order.average_price = order.average_price

Note: this will return all minimum average_price product_id's if there is a tie. Which to me, seems correct otherwise you just picking one product over the other arbitrarily which is not what SQL is supposed to be about. But you could filter the results again by wrapping another aggregate around the above query and then pick a min or max product id to ensure you will get only one result per order.