Results 1 to 10 of 10

Thread: newbie help

  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: newbie help

    Hi

    I have a database with three fields Fund, Date and Price and want to be able to send a query to it so I get the following

    Fund Last_Date_Entered Price_at_last_date
    Fund1 1/12/03 1.12
    Fund2 31/10/03 6.65
    etc....

    I have tried the following simple query

    SELECT Fund, Max(Date) AS Last_Date_Entered , Last(Price) AS Price_at_last_date
    FROM Database
    ORDER BY Date
    GROUP BY Fund

    with no success as the price it returns a price not necessarily at the last date even though I have sorted by Date?? I have tried using sub-queries with HAVING and WHERE clauses to no avail so any help would be most appreciated. I can easily get the maximum date (ie last entry date) but can't for the life of me get the price corresponding to this date is there a function I don't know about? Cheers

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Not 100% sure, but I assume what you are looking for is the Last Price on the Last Date. If this is the case, will it not work to use the following:

    Code:
    SELECT fund, max(date) as last_date, price as price_at_last_date
    FROM database
    ORDER BY date, fund;
    JoeB

  3. #3
    Join Date
    Dec 2003
    Posts
    74
    TRY THIS

    SELECT
    Fund, Date , Price
    FROM Database
    ORDER BY Date DESC <-- THIS WILL DISPLAY THE LATEST FIRST


    REGARDS

    EDWINJAMES
    Last edited by edwinjames; 12-16-03 at 12:30.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    What is Last()?
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Dec 2003
    Posts
    74

    Wink

    apologies ignore the last() bit look at it again

  6. #6
    Join Date
    Dec 2003
    Posts
    74
    or if u just want the latest price then enter the following

    SELECT
    FUND, DATE AS LAST_DATE_ENTERED , PRICE AS PRICE_AT_LAST_DATE
    FROM DATABASE
    WHERE DATE SELECT MAX(DATE) FROM DATABASE

  7. #7
    Join Date
    Dec 2003
    Posts
    3
    Just to clarify there are a number of entries in the database under each fund at different dates.... So firstly I want to group the Funds using a subquery perhaps

    Fund1
    ..... Date, Price
    ..... 12/01/03, 1.15
    ..... 16/02/03, 1.21
    Fund 2
    ..... Date, Price
    ..... 15/01/03, 3.23
    ..... 19/02/03, 4.01
    etc.

    Then select the last date entry and its corresponding price

  8. #8
    Join Date
    Dec 2003
    Posts
    74
    TRY THIS

    SELECT FUND, DATE AS LAST_DATE_ENTERED , PRICE AS PRICE_AT_LAST_DATE
    FROM DATABASE
    WHERE (DATE SELECT MAX(DATE) FROM DATABASE
    AND FUND = SELECT DISTINCT(FUND FROM DATEBASE))
    ORDER BY 1,2

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Use GROUP BY with fund and max(date), date may need to be expanded, then apply an INNER JOIN to this result set with the original table to display the price aswell.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Dec 2003
    Posts
    3
    Originally posted by r123456
    Use GROUP BY with fund and max(date), date may need to be expanded, then apply an INNER JOIN to this result set with the original table to display the price aswell.
    Thanks this worked a treat using two INNER JOINS one between Query1 to find the Last_Date_Entered AND Date of the Database then the other on the Fund. Here is the code

    Query1
    ______
    SELECT Fund, Max(Date) AS LAST_DATE_ENTERED
    FROM DAtabase
    GROUP BY Fund

    Query2
    ______
    SELECT Query1.Fund, AVG(Database.Price)
    FROM Query1 INNER JOIN Database ON (Query1.Fund = Database.Fund) AND (Query1.LAST_DATE_ENTERED = Database.Date)
    GROUP BY Query1.Fund

    The average is necessary if there are two prices entered for any one date equally MAX could have been used. Thanks once again to all those who posted. Derek

Posting Permissions

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