CODEWITHSUNDEEP
sql-servergroup-bymax
Mike S
Mike S

Reputation: 157

SQL get row values for MAX() grouped by 2 columns

Following on from SQL Return every row even if Null values I need to SELECT the row data associated with the MAX(contractPrice) when grouped by two columns.

I've set up sqlfiddle.com/#!6/170043/2 to demonstrate that the problem is the code below returns duplicates (see 5th and 6th lines of sqlfiddle results) if the max price is repeated.

    SELECT
        t1.MeasurableID,
        t1.EntityID,
        t1.ContractPrice,
        ID,
        UTCMatched,
        NumberOfContracts,
        CurrencyCode
    FROM
        contracts AS t1
    INNER JOIN (
        SELECT
            MeasurableID,
            EntityID,
            MAX (contractprice) AS MAXprice
        FROM
            contracts
        WHERE
            MeasurableID IN (2017, 2018)
        AND CurrencyCode IN ('CAD', 'BTC')
        GROUP BY
            MeasurableID,
            EntityID
    ) t2 ON t1.MeasurableID = t2.MeasurableID
    AND t1.EntityID = t2.EntityID
    AND t1.ContractPrice = t2.MAXprice
    WHERE
        t1.MeasurableID IN (2017, 2018)
    AND t1.CurrencyCode IN ('CAD', 'BTC')
    ORDER BY
        MeasurableID,
        EntityID,
        UTCMatched

Upvotes: 0

Views: 84

Answers (3)

Mike S
Mike S

Reputation: 157

P.S. I can eliminate duplicates by using Row_Number() -- per code below:

        SELECT MeasurableID
             ,EntityID
             ,ContractPrice
             ,ID AS HighTradeID
             ,UTCMatched AS HighTradeUTC
             ,NumberOfContracts AS HighTradeNumberOfContracts
             ,CurrencyCode AS HighTradeCurrency
FROM
        (SELECT *
                        ,ROW_NUMBER() OVER(PARTITION BY MeasurableID, EntityID ORDER BY ContractPrice DESC, ID DESC ) RowNumber
        FROM Contracts t1
            WHERE
                MeasurableID IN (2017, 2018)
            AND CurrencyCode IN ('CAD', 'BTC')
        ) AS InnerSelect

WHERE InnerSelect.RowNumber = 1

Upvotes: 0

Simon
Simon

Reputation: 1211

There are a few things that are very confusing or need clarification here:

  1. Your tag is Sql-Server, but you provided us with a sample with MySql
  2. The usage of a derived table doesn't seem necessary, in fact it makes it a bit confusing.

With that said, if you want the MAX ContractPrice with its MAX ID this can be accomplished several ways. Also please note I did not aggregate the rest of the columns properly, which is bad practice. Just wrap the rest of the columns that have not been aggregated or grouped in a MAX():

SELECT 
    t1.MeasurableID,
    t1.EntityID,
    MAX(t1.contractprice) AS MAXprice,
    MAX(t2.ID) as ID,
    MAX(t2.UTCMatched) as UTCMatched,
    MAX(t2.NumberOfContracts) AS NumberOfContracts,
    MAX(t2.CurrencyCode) AS CurrencyCode

FROM
    contracts t1
LEFT JOIN contracts t2 ON t1.MeasurableID = t2.MeasurableID
                          AND
                          t1.EntityID = t2.EntityID
                          AND
                          t1.ContractPrice = t2.ContractPrice

WHERE
    t1.MeasurableID IN (2017, 2018)
AND t1.CurrencyCode IN ('CAD', 'BTC')
GROUP BY
    t1.MeasurableID,
    t1.EntityID

The other method is, if this is actually SQL-Server, you can use ROW_NUMBER and a derived table:

SELECT MeasurableID
       ,EntityID
       ,ContractPrice
       ,ID
       ,UTCMatched
       ,NumberOfContracts
       ,CurrencyCode
FROM
(SELECT *
        ,ROW_NUMBER() OVER(PARTITION BY EntityID ORDER BY ContractPrice, ID DESC ) rn
FROM Contracts t1) t1
WHERE t1.rn = 1

Upvotes: 1

Stéphane CLEMENT
Stéphane CLEMENT

Reputation: 372

Your query can by simplifed and "corrected" :

   SELECT
        t1.MeasurableID,
        t1.EntityID,
        t1.ContractPrice,
        t1.ID,
        t1.UTCMatched,
        t1.NumberOfContracts,
        t1.CurrencyCode
    FROM contracts AS t1
    INNER JOIN contracts AS T2 ON (t1.MeasurableID = t2.MeasurableID AND MeasurableID IN (2017, 2018) AND CurrencyCode IN ('CAD', 'BTC'))
    ORDER BY
        t1.MeasurableID,
        t1.EntityID,
        t1.ContractPrice,
        t1.ID,
        t1.UTCMatched,
        t1.NumberOfContracts,
        t1.CurrencyCode
    HAVING t1.ContractPrice = MAX(T2.contractprice)

But I thing you will still have duplicates... I think your whole query concept is wrong.

Upvotes: 0

Related Questions