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?
There are several options, they all are different ways to sort your records by bid amount so that the first record is the record of the winning bid.
You could sort a found set of records.
A portal can be sorted (and can be a one row portal)
A relationship can be sorted
ExecuteSQL can also use sorting on the bid amount to return data from the record with the winning bid