CODEWITHSUNDEEP
mysqlsqljoin
iforwms
iforwms

Reputation: 381

MySQL ORDER BY, GROUP BY within a JOIN

I've been searching for a while and couln't find an example that worked. Hopefully you can spot the glaring mistake!

SELECT
        Timestamp
        , i_currency.Code AS Code
        , Conversion
    FROM
        i_convert(
            SELECT
                Timestamp
                , Conversion
            FROM
                i_convert
            ORDER BY Timestamp DESC
        )
        JOIN i_currency
            ON i_convert.CurrencyID = i_currency.CurrencyID
    GROUP BY Code

I'm not sure wherer the JOIN should be, should it be in the parentheses, outside or both? I've tried all three with no luck, keep getting: 

You have an error... ...near '( SELECT Timestamp , Conversion FROM i_convert ORDER '

Upvotes: 0

Views: 102

Answers (1)

John Bollinger
John Bollinger

Reputation: 180286

The original query doesn't make sense. It is using GROUP BY, but attempting to select columns that are neither grouping columns nor aggregate functions of the groups. Which Timestamp and which Conversion are expected for any given result row?

I suppose the objective is to select the most recent conversion for each currency. That might look more like this:

SELECT
    latest.Timestamp AS Timestamp
    , i_currency.Code AS Code
    , i_convert.Conversion AS Conversion
FROM i_currency
    JOIN i_convert
        ON i_convert.CurrencyID = i_currency.CurrencyID
    JOIN (
        SELECT MAX(Timestamp) as Timestamp, CurrencyId
        FROM i_convert
        GROUP BY CurrencyId
    ) latest
        ON i_convert.Timestamp = latest.Timestamp
            AND i_convert.CurrencyId = latest.CurrencyId

Upvotes: 1

Related Questions