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? CheersNot 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:

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

JoeB|||TRY THIS

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

REGARDS

EDWINJAMES|||What is Last()?|||apologies ignore the last() bit look at it again|||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|||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|||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|||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.|||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