Selecting one record from each group 1

[Griffyn]

Hi all,

I have a detail table with a CurrencyID, Conversion Rate and Conversion Date. Each CurrencyID will have multiple records for different dates that the conversion rate was changed.

How can I get a query to return the latest rate for each CurrencyID?

I can't use a simple WHERE clause because there may not be a record for each CurrencyID for every date, let alone today's date.

I can make changes to the database, perhaps the Currency master table should get updated with the latest date and conversion rate? That would make things simple. I like to avoid static fields like this though.

Thoughts anyone?

 

[Griffyn]

ok, I got this to work:

SELECT CurrencyHeader.CurrencyID, CurrencyHeader.Description, CurrencyDetail.Date, CurrencyDetail.Conversion
FROM CurrencyHeader INNER JOIN CurrencyDetail ON CurrencyHeader.CurrencyID = CurrencyDetail.CurrencyID
WHERE DMax("Date","CurrencyDetail","CurrencyID='" & [CurrencyHeader.CurrencyID] & "'")=[Date]
ORDER BY CurrencyHeader.CurrencyID;

If anyone has a solution that doesn't involve a domain aggregate function, I'd appreciate it.

 

[PHV]

Seems like JetSQL:
SELECT H.CurrencyID, H.Description, D.Date, D.Conversion
FROM (CurrencyHeader H
INNER JOIN CurrencyDetail D ON H.CurrencyID = D.CurrencyID)
INNER JOIN (
SELECT CurrencyID, Max([Date]) AS LastDate FROM CurrencyDetail GROUP BY CurrencyID
) L ON D.CurrencyID = L.CurrencyID AND D.Date = L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Griffyn]

ah. Haven't got my head around subqueries just yet. They make perfect sense when shown to me, but I need to work on constructing them myself.

That works great, and I'm sure far more efficiently. Thanks PHV!