Getting highest results in a JOIN
- by Keithamus
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_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
This gets the single rows I am after, but it seems to display the lowest bid_amt, not the highest.
