Nikolay Marinov wrote:

>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!
>
>

I have two tables:

Currency
CurrencyIDID integer
Code varchar
RerefenceDate Date
ReferenceValue Numeric

CurrencyHistory
CurrencyHistoryID Integer (PK)
CurrencyID integer (FK -> Currency Table)
Date Date
Value Numeric

Managed by triggers I update the Currency.ReferenceValue and
Currency.ReferenceDate to reflect the last value and date for each
Currency as records are inserted/deleted/updated on CurrencyHistory
Table. This way I just use the currency table and don't need to get
(calculate, summarize ?) the last value for a Currency everytime I need
it. If you want to be a purist on normalization, you could just keep the
LastCurrencyHistoryID on table currency to keep the last record for the
Currency History on the Currency Table, this way you could do a join
between those tables and get the last Currency value/date.

But, to answer your question, try this:

select
A.Currency,
(select Max(B.Date) from CurrencyHistory B where B.Currency =
A.Currency),
(select Max(B.Rate) from CurrencyHistory B where B.Currency =
A.Currency and B.Date = (select Max(C.Date) from CurrencyHistory C where
C.Currency = B.Currency))
from
CurrencyHistory A
where
A.Currency = 'USD'

I think will be easier if you divide the CurrencyHistory Table in two
(one for the currency and another for the daily rates), and IMHO I would
keep the last values denormalized on the Currency Table, and kept up to
date by triggers.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.6.1 - Release Date: 04/03/2005