I have a table called order
which contains columns id
, user_id
, price
and item_id
. Item prices aren't fixed and I would like to select each item's most expensive order. I want to select user_id
, item_id
and price
in the same query. I tried the following query but it doesn't return the correct result set.
SELECT user_id, item_id, MAX(price)
FROM order
GROUP BY item_id
Some of the rows returned by this query have the wrong user_id. However, all rows in the result set show each item's correct highest price.