SQL Query Help

[MRX]

Hi Guys

 

Should be a simple one this, but I just cant get my head round it..

 

Simple table in the format:

 

Ticker TickerDate Close

JJB.L 08/04/2009 13.5

JJB.L 07/04/2009 12.75

JJB.L 06/04/2009 11

JJB.L 05/04/2009 10.75

JJB.L 02/04/2009 11

JJB.L 01/04/2009 10.75

JJB.L 31/03/2009 11.25

 

3 Fields

Ticker (text)

Date (Date)

Close (Number)

 

There are 100+ ticker symbols with data going back a number of years. I need a query that pulls out the latest 200 records (by date) for each Ticker so that I can then use the results to calculate moving averages.

 

Any help is much appreciated!

 

Thanks

 

Rob

[Wez]

How about something like :-

 

select * from tablename where TickerDate between (sysdate-200) and sysdate;

[MRX]

I can see what you're trying to do there but that wont quite work as there is data for every date (no data for weekends). It could possibly work if it used some kind of Networkdays (excel) type function was used?

 

I believe it needs some kind of sort on date then a 'Top 200' used, but it gets complicated as I need Top 200 for each Ticker...

[Wez]

Not sure I follow what you are trying to achieve, the query I provided would pull all entries for the current date and 200 days previous based on what your system date is set too

 

If you then wanted this data ordered you could simply add an order function to the end.

[MRX]

Hi Wez, I need the last 200 records for each ticker, there isn't data for every single day otherwise your solution would be spot on! There isn't any data for weekends/bank holidays so going back 200 days from todays date wont return 200 days worth of data. Which is why I believe a select Top 200 is needed?

[Wez]

Ahhh I see, how about :-

 

select * from tablename where rownum

[MRX]

Ahhh I see, how about :-

 

select * from tablename where rownum

 

I think you're on the right track, but I need the last 200 for each Ticker so if I have 100+ Tickers then I should get 20,000+ records returned.

[Jake]

Is this Oracle, DB2, MS SQL Server (ver?) or what?

 

I'm guessing SQL Server? Just do a subquery using RANK to get the top (ie newest) dates for each Ticket and it'll be plain sailing from there.

[MRX]

Access 2003 Jake.

 

The closest I can get is:

 

SELECT [stock Data BK].Symbol, [stock Data BK].TickerDate, [stock Data BK].Close

FROM [stock Data BK]

WHERE ((((select count(*) from [stock Data BK] as f

where f.symbol = [stock Data BK].symbol and f.TickerDate

))

 

But this is fairly slow and appears to pull out the first 200 records for each Ticker rather than the last 200.

[OhGod]

SELECT TOP 200 *

FROM [stock Data BK]

ORDER BY TickerDate DESC

 

Should pull out the bottom 200 entries, although they'll be in reverse order. As you say you're just doing an average though, this shouldn't matter.

[MRX]

SELECT TOP 200 *

FROM [stock Data BK]

ORDER BY TickerDate DESC

 

Should pull out the bottom 200 entries, although they'll be in reverse order. As you say you're just doing an average though, this shouldn't matter.

 

Sorry mate, not quite right... It needs to pull out the last 200 records for each Ticker, your solution just pulls out the last 200 records.

 

This problem is doing my head in, am getting close to just writing a query for each Ticker and union them all together...

[grahamc]

Hi Guys

 

Should be a simple one this, but I just cant get my head round it..

 

Simple table in the format:

 

Ticker TickerDate Close

JJB.L 08/04/2009 13.5

JJB.L 07/04/2009 12.75

JJB.L 06/04/2009 11

JJB.L 05/04/2009 10.75

JJB.L 02/04/2009 11

JJB.L 01/04/2009 10.75

JJB.L 31/03/2009 11.25

 

3 Fields

Ticker (text)

Date (Date)

Close (Number)

 

There are 100+ ticker symbols with data going back a number of years. I need a query that pulls out the latest 200 records (by date) for each Ticker so that I can then use the results to calculate moving averages.

 

Any help is much appreciated!

 

Thanks

 

Rob

 

Bloody text fields!!

 

This is not actually that simple a query! In access I have no idea... could probably do it with temp tables and a cursor in SQL.

[MRX]

[OhGod]

Doh, didn't catch the each ticker part.

 

As Graham says, it's not really a simple query - or not if you really want all the results in one go. With a full SQL server then, again as Graham says, a cursor and/or temp table would do it.

 

In Access - presuming you have some VB code at the back of it - I think you'd be better off with a quick;

 

SELECT DISTINCT Ticker FROM [stock Data BK]

 

Followed by a TOP query like I mentioned for each Ticker returned. Not as 'clean' as a single query, but as long as you're not accessing the MDB over a VPN or some other slow link the performance shouldn't be much different.

[OhGod]

The only other thing that occurred to me is to add a auto number field to your design then you might get away with something like;

 

SELECT *
FROM [stock Data BK] AS SD
WHERE Id IN (
                     SELECT TOP 200 ID 
                     FROM [stock Data BK] AS SQ 
                     WHERE SQ.Ticker = SD.Ticker
                     ORDER BY TickerDate DESC
                 )

 

Though not sure how fast that'll be in Access (or if it even works, my Access SQL is a bit rusty!)

[MRX]

Thanks mate, off to a meeting now but will test when i get back.

[grahamc]

The only other thing that occurred to me is to add a auto number field to your design then you might get away with something like;

 

SELECT *
FROM [stock Data BK] AS SD
WHERE Id IN (
                     SELECT TOP 200 ID 
                     FROM [stock Data BK] AS SQ 
                     WHERE SQ.Ticker = SD.Ticker
                     ORDER BY TickerDate DESC
                 )

 

Though not sure how fast that'll be in Access (or if it even works, my Access SQL is a bit rusty!)

 

sadly, dont think that will work...

 

he wants 100+ records * latest 200 records for each, which should (for simplicity sake) return 20000+ records. The above will return 200.

 

You will need to dynamic build up each query or result set separately.

[OhGod]

Are you sure? How I read it it's correct, though I think it's likely to be very slow.

 

As I read it it's saying for each row in the source table, do a sub-query to grab the bottom 200 rows where the ticker matches the ticker from the current row in the outer table. If the Id from the outer table is in the result from this sub-query then include this (outer) row in the result set.

 

It certainly seems to work on a table I have here.

 

As it's running - bar any caching mechanic - the sub-query for each row in the source table the performance is going to be, well, less than optimal!

 

A quicker way will probably be to grab the list of distinct Ticker values, then do a query for each as mentioned in previous post.

 

MRX, you might want to add a ORDER BY Ticker, TickerDate onto the outer query too.

[grahamc]

Are you sure? How I read it it's correct, though I think it's likely to be very slow.

 

As I read it it's saying for each row in the source table, do a sub-query to grab the bottom 200 rows where the ticker matches the ticker from the current row in the outer table. If the Id from the outer table is in the result from this sub-query then include this (outer) row in the result set.

 

It certainly seems to work on a table I have here.

 

As it's running - bar any caching mechanic - the sub-query for each row in the source table the performance is going to be, well, less than optimal!

 

A quicker way will probably be to grab the list of distinct Ticker values, then do a query for each as mentioned in previous post.

 

MRX, you might want to add a ORDER BY Ticker, TickerDate onto the outer query too.

 

sorry mis-read... it wont work, as you are reference a table that does not exist within the query or sub-select.

 

you will need to change the where to a join statement

[OhGod]

Don't follow you Graham...the only table referenced is the [stock Data BK] one, it's just aliased differently in the outer and inner query. The WHERE should work for comparing an inner query value against a value from the outer query...it certainly does from within MSSQL and I'm pretty sure Access supports similar.

 

As a slight aside, http://stackoverflow.com/ is good for programming related questions and answers and I've seen it billed as 'like ExpertsExchange, but less evil'.

[grahamc]

SELECT *
FROM [stock Data BK] AS SD
WHERE Id IN (
                     SELECT TOP 200 ID 
                     FROM [stock Data BK] AS SQ 
                     WHERE SQ.Ticker = SD.Ticker --table reference SD does not exist in this sub-select.
                     ORDER BY TickerDate DESC
                 )

 

still dont think this will return the result required... If there is more than 200 IDs in the table you wont get the full record set.

 

this sort of extract will have to be looped through to retrieve the 200 records per ID.

[OhGod]

SD.Ticker refers to the table in the outer query, and is re-evaluated for each row in the outer query - hence why this query will be slow as it has to re-run the inner query for each outer query row.

 

Have you tried similar in a database your end and are getting that error? As mentioned earlier I'm trying with MS SQL Server and it works fine - same format query, just different table/field names substituted. I was able to get the last 5 (as a test) orders for each of our partners and the table has thousands of rows. Might not work with other databases of course.

 

Still think separate queries for each Ticker Id will actually produce better performance, but have to wait for MRX to get back to us I guess.

[MRX]

Thanks for all the help and suggestions guys, much appreciated!

 

Well, after sleeping on the problem I have tackled it from another angle today and here's my solution:

 

First step was to add an index (auto number) to the table, and because my import routine pulls in all the data for each ticker one at a time it means that all the data for each ticker is in sequence e.g.

 

AAAA 1-1500

BBBB 1501- 3000

CCC 3001-4500

 

What that means is I can create a query that identifys the first index for each symbol.

 

Then I created another query linked to the above that pulls out all data for each symbol where the index is less than the first index+200.

 

SELECT Quotes.Index, Quotes.Ticker, Quotes.Date, Quotes.Close

FROM Quotes INNER JOIN [Ticker Last Index] ON Quotes.Ticker = [Ticker Last Index].Ticker

WHERE (((Quotes.Index)

ORDER BY Quotes.Date DESC;

 

Runs pretty fast so im happy