I've got a table of exchange rates:
EXCH_RATE
----------------------------------
id | name | amount | rate | date_set
1 c1 1 100 2004-01-01
2 c2 100 86 2004-01-01
3 c3 1 25 2004-01-01
1 c1 1 101 2004-01-10
2 c2 100 87 2004-01-10
1 c1 1 110 2004-02-01
2 c2 100 90 2004-02-01
3 c3 1 26 2004-02-01

for a particular date there is an appropriate exchange rate. If rate is not changed for a given date it comes from the last changed date. Now I'd like to do a query to find out what is the current exchange rate for all currencies. I can do it for one currency, say with id 2 like this
SELECT * FROM EXCH_RATE
WHERE id = 2 AND
date_set = (SELECT max(date_set) FROM EXCH_RATE WHERE id = 2)

result:
id | name | amount | rate | date_set
2 c2 100 90 2004-02-01

The question is how to retirieve the last exchange rates for all currencies?
Thank you in advance

P.S. I'm using DB2 if this info is needed

I'm not sure of the exact syntax with DB2. In SQL Server, we can do this:

SELECT
id,
name,
amount,
rate,
date_set
FROM
EXCH_RATE er1
INNER JOIN (
SELECT MAX(date_set) AS date_set, id
FROM EXCH_RATE) er2 ON er1.date_set = er2.date_set
AND er1.id = er2.id

I would suggest using:This should work for a specific id or all id values.

-PatP

???? So will the query I provided. You just need to add a where clause to it if you want to pass a paramater in and only do it for one value. It would be the same with your query.

I don't think that your query will do that on DB2 (UDB or Z-Series, I don't know enough about DB2/400 to have a useful opinion), although I haven't actually tested it. I think that my query will work on all of the DB2 versions (again, I haven't tested that either).

-PatP

Kewl. I haven't used DB2 in a long time. I can't remember anything about how it worked from the little I did. lol

thank you guys.