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

Answers


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)

Query

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

Result


Need Your Help

How to bind akka http with akka streams?

scala akka akka-stream akka-http

I'm trying to use streams instead of pure actors to handle http requests and I came with the following code:

Show, Show Detail, Present, Present As Segue not available in Xcode

ios swift interface-builder xcode7

I use Xcode 7.2, I've a problem with segue when I try to perform it What I see is current new segue options are not available but instead I still see is deprecated versions of segue like this: