Getting highest results in a JOIN

Question

I've got three tables; Auctions, Auction Bids and Users. The table structure looks something like this:

Auctions:

 id    title
 --    -----
  1     Auction 1
  2     Auction 2

Auction Bids:

 id    user_id    auction_id    bid_amt
 --    -------    ----------    -------
  1       1            1          200.00
  2       2            1          202.00
  3       1            2          100.00

Users is just a standard table, with id and user name.

My aim is to join these tables so I can get the highest values of these bids, as well as get the usernames related to those bids; so I have a result set like so:

auction_id    auction_title    auctionbid_amt    user_username
----------    -------------    --------------    -------------
         1    Auction 1            202.00            Bidder2
         2    Auction 2            100.00            Bidder1

So far my query is as follows:

SELECT a.id, a.title, ab.bid_amt, u.display_name FROM auction a
    LEFT JOIN auctionbid ab ON a.id = ab.auction_id
        LEFT JOIN users u ON u.id = ab.user_id
GROUP BY a.id

This gets the single rows I am after, but it seems to display the lowest bid_amt, not the highest.

Answer 1

You can use the MAX-Function and a sub-select to get the maximum bid for each auction. If you join this subselect with your other tables and set the where clause as follows you should get what you are looking for.

SELECT a.id, a.title, ab.bid_points, u.display_name 
FROM Auction AS a
INNER JOIN (SELECT auction_id, MAX(bid_points) AS maxAmount FROM auction_bids GROUP BY auction_id) AS maxBids ON maxBids.auction_id = a.id
INNER JOIN auction_bids AS ab ON a.id = ab.auction_id
INNER JOIN users AS u ON u.id = ab.user_id
WHERE ab.auction_id = maxBids.auction_id AND ab.bid_amount = maxBids.maxAmount

Hope that helps.

Answer 2

This is a typical within-group aggregate problem. You can solve it using a so called left self exclusion join

Try the following:

SELECT a.id, a.title, ab.bid_points, u.displayname
  FROM auction a
  INNER JOIN auction_bids ab ON ab.auction_id = a.id
  LEFT JOIN auction_bids b1 ON ab.auction_id = b1.auction_id
    AND ab.bid_points < b1.bid_points
  LEFT JOIN users u ON u.id = ab.user_id
  WHERE b1.auction_id IS NULL

It basically builds a join between the left and right side, until it doesn't find one for the left side anymore, and thats the highest element then.

Another solution would be using multiple querys (of course) or a temporary aggregate table.