Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: Need some help, hint for query

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    SELECT * FROM EXCH_RATE a
    WHERE
    a.date_set = (SELECT max(b.date_set) FROM EXCH_RATE b WHERE b.id = a.id)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2004
    Posts
    7
    Thank you, it was a real quick reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •