We have two tables for storing auctions & it’s bids info.
- auction (which stores the auction info)
2216 Test Auction
- auction_bid (which stores the auction bids)
bid_id amount bidder auction_id
8398 50.0000 53 2216
8397 50.0000 32 2216
8396 20.0000 53 2216
Note: The bidder with max bidding amount is declared as winner. In case of tie bids, the last bidder with max tie amount is declared as winner.
For example in above case winner bidder is 53
Now I want to show all the winning auction info on the user dashboard. For example for bidder = 53
I have used the following Query for the purpose:
SELECT DISTINCT a.*, b.bid_id, b.amount, b.bidder FROM auction a LEFT JOIN auction_bid b ON a.auction_id = b.auction_id WHERE b.bidder = 53 AND b.amount = (SELECT MAX(amount) FROM auction_bid tmp WHERE tmp.auction_id = b.auction_id LIMIT 0, 1)
This works fine for bidder = 53.
But for bidder = 32, the same auction is also shown as winning auction.
I know the issue is due to tie bids. How to modify above query so that it works for tie bids?
Any help is really appreciated.