Andrey Kudriavtsev Andrey Kudriavtsev - 5 months ago 19
SQL Question

MySQL GROUP BY fields mismatch

Is there any way to do this simple thing without two sub-queries?

This is my basic data:

SELECT order_detail_id, product_id, MAX(paid_price) AS max_price, order_id
FROM t_order_details
WHERE order_id = 7 OR order_id = 8
GROUP BY order_id


simple select result

I want select order_detail_id and product_id with maximum price, in order 7 and same thing in order 8. It seems that the data do not match row

SELECT order_detail_id, product_id, MAX(paid_price) AS max_price, order_id
FROM t_order_details
WHERE order_id = 7 OR order_id = 8
GROUP BY order_id


wrong result

This is my solution with two sub-queries.

SELECT order_detail_id, product_id, paid_price, order_id
FROM t_order_details
WHERE paid_price IN (
SELECT MAX(paid_price) AS max_price
FROM t_order_details
WHERE order_id = 7 OR order_id = 8
GROUP BY order_id)
AND order_id IN (
SELECT order_id
FROM t_order_details
WHERE order_id = 7 OR order_id = 8
GROUP BY order_id)
GROUP BY order_id


But I think there should be more natural way to do this

Answer

Try following;)

SELECT T1.order_detail_id, T1.product_id, T1.paid_price, T1.order_id
FROM t_order_details T1
INNER JOIN (
    SELECT MAX(paid_price) AS paid_price, order_id
    FROM t_order_details
    WHERE order_id IN (7, 8)
    GROUP BY order_id
) T2 ON T1.order_id = T2.order_id AND T1.paid_price = T2.paid_price
WHERE T1.order_id IN (7, 8)
Comments