I have a table that keeps exchange rates for multiple currencies:
fromCur, toCur, rate, effectiveDate
USD BRL .1 12/2/04
USD BRL .11 1/1/05
USD CAN .5 11/3/04 ...
and so on for all combinations.
I need to get the most recent rate for a given fromCur, for _EACH_ toCur and I can't think of a way to do it with out a procedural approach that:
1. Gets all ToCur's for a given fromCur (usd)
2. For each toCur, select top 1 rate from <table> where fromCur = 'usd' and toCur = @toCur order by effDate Desc

IS there a t-SQL only way to do this?
Thank you for any input.

You can do this wth a correlated subquery. Something like this

SELECT fromCur, toCur, rate, effectiveDate
FROM ExchangeRates er1
WHERE fromCur = @fromCur
AND effectiveDate = (
    SELECT MAX(effectiveDate)
    FROM ExchangeRates er2
    WHERE er1.fromCur = er2.fromCur
     AND er1.toCur = er2.toCur
)

hth
Phil

another way to do this without a correlated subquery (and possibly easier to understand) is like this:

SELECT er1.fromCur, er1.toCur, er1.rate, er1.effectiveDate
FROM ExchangeRates er1
INNER JOIN
(SELECT fromCur, toCur, MAX(effectiveDate) as effectiveDate
FROM ExchangeRates
GROUP BY fromCur, toCur) tmp
ON er1.fromCur=tmp.fromCur
   AND er1.toCur=tmp.toCur
   AND er1.effectiveDate=tmp.effectiveDate

correlated subqueries can be expensive, so check out the estimated execution plan to see which way is more efficient for your table.

rgds
Phil

these work great - thanks for the replies.
kb