Results 1 to 7 of 7
  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

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

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would suggest using:
    Code:
    SELECT a.id, a.name, a.amount
    ,  a.rate, a.date_set
       FROM EXCH_RATE AS a
       WHERE  a.date_set = (SELECT Max(b.date_set)
          FROM EXCH_RATE AS b
          WHERE  b.id = a.id)
    This should work for a specific id or all id values.

    -PatP

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    ???? 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.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    May 2004
    Posts
    7
    thank you guys.

Posting Permissions

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