GROUP BY problem

[nando]

I have a table(Prices) with the following fields:-
PriceDate
FundName
BidPrice
OfferPrice
ExchRate
I am trying to select the most recent Price record for each FundName.
So far I have the following:-
SELECT * FROM Prices
WHERE PriceDate = (SELECT Max(PriceDate) FROM Prices)
but this selects only the funds with the most recent date, i.e. if Fund A's most recent date is 01 Oct 2001 but Fund B, C, etc is 05 Oct 2001 then a record for Fund A is not selected.

I have tried adding the following GROUP BY in the Subselect
SELECT * FROM Prices
WHERE PriceDate = (SELECT Max(PriceDate) FROM Prices GROUP BY FundName)
but this won't run at all.

Any ideas?

 

[Bushido121]

Try this.

SELECT * FROM Prices
WHERE PriceDate in (SELECT Max(PriceDate) FROM Prices ORDER BY Max(PriceDate))

 

[Pat Hartman]

I do this with with two queries.

query1:
Select FundName, Max(PriceDate) As MaxPriceDate
From Prices
Group By FundName;

query2:
Select q1.FundName, q1.MaxPriceDate, p.BidPrice, p.OfferPrice, p.ExchRate
From query1 As q1 Inner Join Prices As p;

The first query finds the Max date for each fund and the second query retrieves the associated fields.

 

[araskas]

The following co-related sub query will also do the same for you .

SELECT DISTINCT Outer.Fund, Outer.Price, Outer.Date
FROM myTable, myTable AS [Outer]
WHERE (((Outer.Date)=(SELECT max(Date) FROM myTable1 WHERE Fund = Outer.Fund )));

Take a look at this -
FROM myTable, myTable AS [Outer]
For some reason Access will not let you alias a table without first using its actual name.
In other RDBMSs the following query will work -
SELECT DISTINCT Outer.Fund, Outer.Price, Outer.Date
FROM myTable AS [Outer]
WHERE (((Outer.Date)=(SELECT max(Date) FROM myTable1 WHERE Fund = Outer.Fund )));
Any comments on Alias !!!



[This message has been edited by araskas (edited 11-16-2001).]