I have a database to report on the results of an auction for gemstones. Two bidders bid on each of many stones. The winner of each stone is the bidder with the highest bid. I have worked out so far what the highest bidding is (taking the maximum bid of each stone), and multiply the highest bid by the weight of the stone, to get the total owing by the winner for the stone. But, how do I calculate who was the highest bidder? I can look up manually who made the highest bid, but I want the database to do that for me. The formula I used to look up the bidder name (stone_cdestination) is:
Let ( winner = stone_cmax_bid_amount = Bid::bid_amount; Bidder::bidder_name )
But it's not giving the correct result. It keeps showing Bidder 2 as the winner when I know this is not true.
Any guidance please?