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

Try this:

SELECT a.id, a.title, ab.bid_points, 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
ORDER BY ab.bid_points DESC

If that doesn't work, try using a subselect on auctionbids containing something like

SELECT id, user_id, auction_id, MAX(bid_amt) FROM action_bids GROUP BY auction_id

Answer 2

Try adding the following clause; not sure about performance.

WHERE NOT EXISTS
   (SELECT * FROM auctionbid abhigher
    WHERE abhigher.auction_id = ab.auction_id
    AND   abhigher.auctionbid_amt > ab.auctionbid_amt)

Excludes auction bids from the query that have a higher bid for the same auction.

The only problem is that if you have 2 equal bids and both will list. One way to get rid of them - but it is a relatively arbitrary choice of winner, is to use the bid id:

WHERE NOT EXISTS
   (SELECT * FROM auctionbid abhigher
    WHERE abhigher.auction_id = ab.auction_id
    AND   abhigher.auctionbid_amt >= ab.auctionbid_amt
    AND   abhigher.id > ab.id)

Answer 3

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.

Answer 4

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 5

All ways to get other fields from row with maximum value are described in link text

Answer 6

Here is what you can try..like old school..nothing new..no need to go for left join or anything else..rest depends on your exact requirement

select A.id,A.title,max(AB.bid_amt),name
from Auction A,AuctionBids AB,Users U
where U.ID=AB.USER_ID AND A.ID=AB.ID
group by A.ID,A.title,name