SQL select join the row with max (arithmatic(value1, value2))
I am trying to make a Trade system where people can make offer on the items they want. There are two currencies in the system, gold and silver. 100 silver = 1 gold. Note that people can make offers the same price as others, so there could be duplicate highest offer price.
Table structure looks roughly like this
Trade table ID TradeOffer table ID UserID TradeID references Trade(ID) GoldOffer SilverOffer
I want to display to the user a list of trades sorted by the highest offer price whenever they do a search with constraint.
The Ideal output would be similar to this
Trade.ID TradeOffer.ID HighestGoldOffer HighestSilverOffer UserID
where HighestGoldOffer and HighestSilverOffer are the value of GoldOffer and SilverOffer column of the Offer with highest (GoldOffer * 100 + SilverOffer) and UserID is the user who made the offer
I know I can run 2 separate queries, one to retrieve all the Trades that satisfies all the constraint and extract all the ID to run another query to get the highest offer, but I am a perfectionist so I would prefer to do it with one sql instead of two.
I could just select all offers that are (GoldOffer * 100 + SilverOffer) = MAX (GoldOffer * 100 + SilverOffer) but this would possibly return duplicated Trade if there are multiple people offered the same price. Also there could be nobody offered on the Trade yet so GoldOffer and SilverOffer will be empty, I would still like to show the Trade as no offer when this happened.
Hope I made myself clear and thanks for any help
Model and test data
CREATE TABLE Trade (ID INT) CREATE TABLE TradeOffer ( ID INT, UserID INT, TradeID INT, GoldOffer INT, SilverOffer INT ) INSERT Trade VALUES (1), (2), (3) INSERT TradeOffer VALUES (1, 1, 1, 10, 15), (2, 2, 1, 11, 15), (3, 1, 2, 10, 16), (4, 2, 2, 10, 16)
SELECT [TradeID], [TradeOfferID], [HighestGoldOffer], [HighestSilverOffer], [UserID] FROM ( SELECT t.ID AS [TradeID], tOffer.ID AS [TradeOfferID], tOffer.GoldOffer AS [HighestGoldOffer], tOffer.SilverOffer AS [HighestSilverOffer], tOffer.[UserID], RANK() OVER ( PARTITION BY t.ID ORDER BY (([GoldOffer] * 100) + [SilverOffer]) DESC ) AS [Rank] FROM Trade t LEFT JOIN TradeOffer tOffer ON tOffer.TradeID = t.ID ) x WHERE [Rank] = 1