SQL select rows in a JOIN with max value on a column

Question

I'm selecting values from 3 different tables to get an overview of some product orders. Without MAX, no issues.

Here's the data I'm working with:

-- limited to first rows for the sake of the exemple
+------+---------------------+-------------------------------+-------+
| ID   | post_date           | order_item_name               | price |
+------+---------------------+-------------------------------+-------+
| 2348 | 2019-01-23 18:47:34 | product A                     | 18.9  |
| 2348 | 2019-01-23 18:47:34 | Product B                     | 4.5   |
| 2348 | 2019-01-23 18:47:34 | Product C                     | 50.5  |
| 2349 | 2019-01-23 21:59:04 | Product E                     | 26.5  |
| 2352 | 2019-01-24 07:41:12 | Product C                     | 50.5  |
+------+---------------------+-------------------------------+-------+

These are returned by the following SQL query.

SELECT 
    p.ID AS order_id,
    post_date,
    order_item_name,
    meta_value as price
FROM wp_posts AS p
JOIN wp_woocommerce_order_items
ON p.ID = order_id
JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
WHERE 
    post_type = 'shop_order' 
    AND post_status = 'wc-completed'
    AND meta_key = '_line_subtotal';

Now what I would like is get only the most expensive product from each order. Obviously, just using the MAX function with GROUP BY returns one line per order, but the product name doesn't match the price.

SELECT 
    p.ID AS order_id,
    post_date,
    order_item_name,
    MAX(meta_value) AS price
FROM alpha_posts AS p
JOIN alpha_woocommerce_order_items
ON p.ID = order_id
JOIN alpha_woocommerce_order_itemmeta
ON alpha_woocommerce_order_items.order_item_id = alpha_woocommerce_order_itemmeta.order_item_id
WHERE 
    post_type = 'shop_order' 
    AND post_status = 'wc-completed'
    AND meta_key = '_line_subtotal'
GROUP BY order_id;

That returns the highest price but the order_item_name column doesn't correspond to the given price.

+----------+---------------------+-------------------------------+-------+
| order_id | post_date           | order_item_name               | price |
+----------+---------------------+-------------------------------+-------+
|     2348 | 2019-01-23 18:47:34 | Product A                     | 50.5  | -- should be product C
|     2349 | 2019-01-23 21:59:04 | Product B                     | 26.5  | -- product b is 4.5, so it's clearly not matching (same for the following results)
|     2352 | 2019-01-24 07:41:12 | Product A                     | 60.9  |
|     2354 | 2019-01-25 07:43:36 | Product C                     | 23.1  |
|     2355 | 2019-01-26 19:59:31 | Product D                     | 79.9  |
+----------+---------------------+-------------------------------+-------+

I've managed to find exemples for one-table queries, but I'm helpless for this multi-join one.

Answer 1

As expected, aggregate functions work on one column without considering other columns, they are not filters.

That's why the MAX function returns the maximum value met in the designated column, but others column are not the ones that correspond to the max value selected (or any result of an aggregate function).

In order to select the matching columns based on the max value, we can use a JOIN query, in our case, joining both on order_id and price.

SELECT 
    ID, 
    post_date,
    wp_woocommerce_order_items.order_item_name,
    wp_woocommerce_order_itemmeta.meta_value
FROM wp_posts
JOIN wp_woocommerce_order_items
ON ID = order_id
JOIN wp_woocommerce_order_itemmeta
ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
JOIN (
    SELECT
        order_id,
        MAX(meta_value) as price
    FROM wp_woocommerce_order_items
    JOIN wp_woocommerce_order_itemmeta
    ON wp_woocommerce_order_items.order_item_id = wp_woocommerce_order_itemmeta.order_item_id
    WHERE meta_key = '_line_subtotal'
    GROUP BY order_id
) b
ON ID = b.order_id AND wp_woocommerce_order_itemmeta.meta_value = price
WHERE 
    post_type = 'shop_order' 
    AND post_status = 'wc-completed'
    AND meta_key = '_line_subtotal';