I'm no good at stored procedures (the syntax always scared me) but ... it seems you could write one
that:

a) internally retrieves the whole history, sorted by currency (any order) then by date (descending)

b) as it loops through the results, only returns a row if the currency is different from the
previous row's currency (and if first row, always return), then ignore the rest of the rows until
the currency chances again

As nobody else was answering, I felt I could at least suggest a general direction ...

The -nasty- way of solving this with pure SQL is to do the following:

create table distinct_currencies (varchar(xxx) currency);
create index i_distinct_currencies_a on distinct_currencies (currency);
insert into distinct_currencies
select distinct currency from tCurrencyHistory;

(or create a view? create view distinct_currencies as select distinct currency from
tCurrencyHistory;)

select distinct_currencies.currency, (select first 1 rate from tCurrencyHistory where id = (select
first 1 id from tCurrencyHistory where currency = distinct_currencies.currency order by date desc))
from distinct_currencies;

(extra "first 1" in there, but there's something about firebird needing to know, and not knowing
without your help, that the sub-select only returns one row?)

Just some ideas. Not tested. We store everything as differences and run the sum (total) for each
type of item (currency in your case, accounts in our case.) I hear it's not good for
accountability, because unlike book-keeping on paper, it's really easy to hide your tracks
modifying rows in a db.

Regardless, best of luck. Let us know what worked for you.

-Philip

---------- Original Message -----------
From: "Nikolay Marinov" <kok_bg@...>
To: firebird-support@yahoogroups.com
Sent: Tue, 08 Mar 2005 17:34:18 -0000
Subject: [firebird-support] Need an example

> Hi
> Using Firebird 1.5
>
> I have table tCurrencyHistory
>
> ID DATE CURRENCY RATE
> ===========================================
> 1 01.01.05 USD 1.51
> 2 01.01.05 EUR 1.96
> 3 02.01.05 USD 1.50
>
> i need to select only the last currency rates for each currency:
> result:
> ID DATE CURRENCY RATE
> ===========================================
> 2 01.01.05 EUR 1.96
> 3 02.01.05 USD 1.50
>
> Please, give me an example how to do this, because i try with
> Max(DATE) but it doesn't work because of Group By clause for RATE
> column - it return each value
>
> Thanks in advance!
>
> Yahoo! Groups Links
>
>
>

------- End of Original Message -------