GROUP BY query problem

[dilbert]

Hi again, I experience a problem with group by condition in ling queries.

I'm trying to "get row with the group-wise maximum".
More specific: For each currency get its latest exchange rate.

Here is my LINQ solution:

Code: Select all

from t1 in DB.CurrencyRates
where (from t2 in DB.CurrencyRates
           group t2 by t2.Id_currency into grouped
           select grouped.Max(g => g.Date)).Contains(t1.Date)
select t1;

I got an exception:

Code: Select all

Unknown column 't1.Id_currency' in 'field list

Here is translated SQL code:

Code: Select all

SELECT t1.Id, t1.Id_currency, t1.Date_set, t1.Rate
FROM DB.currency_rate t1
WHERE EXISTS
    (
        SELECT t2.C1
        FROM (
            SELECT t1.Id_currency, MAX(t3.Date_set) AS C1
            FROM DB.currency_rate t3
            GROUP BY t1.Id_currency
            ) t2
        WHERE t2.C1 = t1.Date_set
        )

The problem is in the nested query. There should be t3.Id_currency instead of t1.Id_currency in both SELECT and GROUP BY sections.

Tested on dotConnect for MySQL 5.40.49 and 5.50.52 Beta.

Thank you for attention in advance.

[dilbert]

The same problem occurs even without GROUP BY section:

Code: Select all

from t1 in DB.CurrencyRates
where dcr.Date_set == (from t2 in DB.CurrencyRates
                                   where t2.Id_currency == t1.Id_currency
                                   select t2.Date_set).Max()
select t1;

Translated:

Code: Select all

SELECT t1.Id_currency, t1.Rate
FROM db.currency_rate t1
WHERE t1.Date_set = ((
    SELECT MAX(t1.Date_set) AS C1
    FROM db.currency_rate t2
    WHERE t2.Id_currency = t1.Id_currency
    ))

There is no exception now. However, logically there should be MAX(t2.Date_set) in the nested query.

[AndreyR]

Thank you for the report, i have reproduced the first problem. I will let you know about the results of our investigation.
As for the second problem, in my test project the query was generated properly.

[AndreyR]

We have tested the query you are using in the test project, it gives the same result using both LINQ to SQL Server
and LINQ to MySQL. Looks like the problem is in the query, I recommend you to change it.