CODEWITHSUNDEEP
mysqlsql
emkay
emkay

Reputation: 901

Join row with MAX row in another table?

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.

Upvotes: 8

Views: 11086

Answers (5)

egrunin
egrunin

Reputation: 25053

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.

Upvotes: 12

Chandu
Chandu

Reputation: 82903

Try this:

SELECT a.*, bid_amount
  FROM auction a INNER JOIN 
    (
     SELECT auction_id, MAX(bid_amount)  AS bid_amount
       FROM auction_bids
         WHERE acution_id = x
         GROUP BY auction_id
    ) b
    ON a.auction_id = b.auction_id

Upvotes: 1

Brownarola
Brownarola

Reputation: 1

Basically you have to use a sub query to do this in the actual join. 

Select bid as highest_bid
  from auctions
    left outer join auctions_bids on action_bids.ref_no = auctions.ref_no
     and (select max(bid) from auctions_bids as ab 
           where auctions_bid.ref_no = a2.ref_no)

Upvotes: 0

Amir Ismail
Amir Ismail

Reputation: 3883

Try this

 Select A.ID,max(AB.bit_amount) 
 From  auctions A Inner Join Auction_bids AB on A.ID = AB.auction_ID
 Where A.ID = x
 Group By A.ID

Upvotes: 0

p.campbell
p.campbell

Reputation: 100567

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.

Upvotes: 1

Related Questions