Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Join row with MAX row in another table?

Tags:

sql

mysql

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.

like image 963
emkay Avatar asked Sep 21 '11 16:09

emkay


2 Answers

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.

like image 72
egrunin Avatar answered Nov 14 '22 21:11

egrunin


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.

like image 40
p.campbell Avatar answered Nov 14 '22 23:11

p.campbell