We have two tables for storing auctions & it’s bids info.
- auction (which stores the auction info)
auction_id auction_name
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.
Regards