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

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:

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.

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