GROUP BY query problem
Posted: 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:
I got an exception:
Here is translated SQL code:
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.
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;
Code: Select all
Unknown column 't1.Id_currency' in 'field list
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
)
Tested on dotConnect for MySQL 5.40.49 and 5.50.52 Beta.
Thank you for attention in advance.