How do I join a row from one table to a row with the MAX value for a given column on another table?
For example, I have a auctions
table and an auction_bids
table. I want to join the auctions table with the highest bid for that auction (i.e. highest value for column bid_amount
AND where auction_id
= x
) in the auction_bids
table.
It's annoyingly complicated. You'd be better off with a "winner" flag in each winning auction_bid.
SELECT * FROM auctions a
INNER JOIN
(
/* now get just the winning rows */
SELECT * FROM auction_bids x
INNER JOIN
(
/* how to tell the winners */
SELECT auction_id, MAX(bid_amount) as winner
FROM auction_bids
GROUP BY auction_id
) y
ON x.auction_id = y.auction_id
AND x.bid_amount = y.winner
) b
ON a.auction_id = b.auction_id
Note that auctions with zero bids will not be listed at all, and auctions with ties (can that happen?) will appear once for each tied bid.
Try this:
SELECT a.id, MAX(ab.bid_amount)
FROM auctions AS a
INNER JOIN action_bids AS ab
ON a.id = ab.auction_id
GROUP BY a.id;
Add more columns to your SELECT
and GROUP BY
clauses as needed.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With