MySQL: getting the rest of the string data when using 'max'

Question

I have a table that looks something like this:

user_id   item_id   bid_amount
5         12        22
6         12        47
7         12        40
6         14        55

I am trying to get the highest bid for each item and user_id belonging to that bid. My current attempt:

select user_id, max(bid_amount) from bids group by item_id;

not so spectacular. I think its just user_id from the first line of int he group - is there one request that gets me the data I want?

+3

Will May 08 '12 at 11:11

1 answer

Mark byers · Accepted Answer · 2012-05-08T23:13:50+0000

SELECT user_id, item_id, bid_amount FROM bids 
WHERE (item_id, bid_amount) IN (
   SELECT item_id, MAX(bid_amount) FROM bids GROUP BY item_id
)

See how it works on the web: sqlfiddle