GROUP BY query problem

GROUP BY query problem

Postby dilbert » Fri 30 Oct 2009 13:26

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
 
Posts: 68
Joined: Tue 28 Apr 2009 10:11

Re: GROUP BY query problem

Postby dilbert » Fri 30 Oct 2009 13:53

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.
dilbert
 
Posts: 68
Joined: Tue 28 Apr 2009 10:11

Postby AndreyR » Mon 02 Nov 2009 17:20

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
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Mon 16 Nov 2009 14:06

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to LinqConnect (LINQ to SQL support)