At 01:57 PM 8/03/2005 +0000, you wrote:

>Hi,
>using firebird 1.5
>
>i have table tCurrencyHistory :
>
>ID DATE Currency Rate
>1 01.03.05 USD 1.50
>2 01.03.05 EUR 1.96
>3 02.03.05 USD 1.51
>4 28.02.05 EUR 1.96
>5 28.02.05 GBP 2.05
>
>and i want to retrieve the last rate for each Currency.
>But using max(date) in single select return each different 'Rate'
>value because of Group By clause
>
>select max("tCurrencyHistory"."Date"), "tCurrencyHistory"."Currency",
>"tCurrencyHistory"."Rate" from "tCurrencyHistory" group by
>"tCurrencyHistory"."Currency", "tCurrencyHistory"."Rate"
>
>Please help how to construct the query to return:
>
>ID DATE Currency Rate
>2 01.03.05 EUR 1.96
>3 02.03.05 USD 1.51
>5 28.02.05 GBP 2.05

select
T1.ID,
T1."Date",
T1."Currency",
T1."Rate"
from "tCurrencyHistory" T1
WHERE ID IN (2,3,5)
AND T1."Date" =
( SELECT MAX("T2.Date") FROM "tCurrencyHistory" T2
WHERE T2."Currency" = T1."Currency" )
ORDER BY 1

./hb