CODEWITHSUNDEEP
mysql
Will
Will

Reputation: 5590

mySQL: getting the rest of a row's data when using 'max'

I have a table that looks a bit like this:

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

I'm trying to get the highest bid for a each item and the user_id which belongs to that bid. My current attempt:

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

fails pretty spectacularly. I think its just the user_id from the first row int he group -- is there a single query that gets me the data I want?

Upvotes: 3

Views: 112

Answers (1)

Mark Byers
Mark Byers

Reputation: 838796

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 it working online: sqlfiddle

Upvotes: 3

Related Questions