I have a table which holds a history of Fund Prices. Most Prices change on a daily basis, but this is not always the case. If a price has not changed then it will not get a new entry.
So... the question is.. how do I return a table of prices without doing nasty stuff like nested selects?
The table structure is as follows:
NAME EFFECTIVE_DATE PRICE
FISS 2004-09-13 2200
FISS 2004-09-12 2122
FIBB 2004-09-12 9800
I want to return a table that will show the latest price for each NAME. At the moment I'm doing it like this : (Which is really slow and i'm sure not the correct way)
from Price_Table P1
Where Effective_Date = (Select Max(Effective_Date) from Price_Table P2 Where P1.NAME = P2.Name)
As the Price table is really big, this takes forever.
I am sure there must be a much better way to do this. Something along the lines of grouping the Name against the MAX(Effective_Date) or doing a TOP 1 within a group