Solved

SQL Query to find Latest Price

Posted on 2004-09-14
9
736 Views
Last Modified: 2008-02-01
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



0
Comment
Question by:ggets
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 12055390
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
 

Author Comment

by:ggets
ID: 12055486
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
 
LVL 10

Accepted Solution

by:
AaronAbend earned 63 total points
ID: 12055590
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 10

Expert Comment

by:AaronAbend
ID: 12055598
Note - there may be some syntax missing  in that -- the select list should have p1 prefixes.
0
 
LVL 8

Assisted Solution

by:MartinCMS
MartinCMS earned 62 total points
ID: 12055738
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
 

Author Comment

by:ggets
ID: 12398038
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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
best counters for cpu high usage 3 28
get column names from table in vb.net 8 27
convert null in sql server 12 32
T-SQL:  Collapsing 9 22
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question