SQL Query to find Latest Price

Hi,

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)

Select NAME,PRICE
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

Thanks



ggetsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
AaronAbendConnect With a Mentor Commented:
Not good!

Here's another syntax to try...

Select NAME,PRICE
from Price_Table P1 join (select p2.name, Max(Effective_Date) as p2.maxdate from Price_Table P2 group by p2.name) on P1.NAME = P2.Name  
Where p1.Effective_Date = p2.maxdate

Put your SQL and this into the same query analyzer and analyze them at the same time and see if the strategies are different...
0
 
AaronAbendCommented:
There is no better way to do this really.  Put an index on price_table:

create index datename on price_table(name, effective_date)

That should help a lot. If you already have that index, let me know. There are other tricks. The real question is where the performance is being degraded.
0
 
ggetsAuthor Commented:
well.. actually there is no index on the table, and I won't be able to add one. This is not my database and I cannot change the structure (it is part of a much bigger system)
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
AaronAbendCommented:
Note - there may be some syntax missing  in that -- the select list should have p1 prefixes.
0
 
MartinCMSConnect With a Mentor Commented:
try this...

Select       yt.[NAME],Convert(varchar(10),yt.EFFECTIVE_DATE,101) as EFFECTIVE_DATE,PRICE
From       yourtable yt inner join yourtable ytj on
      yt.[NAME] = ytj.[NAME] and
      Convert(varchar(10),yt.EFFECTIVE_DATE,102) <= Convert(varchar(10),ytj.EFFECTIVE_DATE,102)
Group By yt.[NAME],yt.EFFECTIVE_DATE
Order By yt.[NAME],yt.EFFECTIVE_DATE
0
 
ggetsAuthor Commented:
My Appologies for not getting back to this one. Have been away, sick, over worked etc etc etc etc...

Above suggestions make sence so happy that the points get allocated...

Many Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.