Top / Group Query

[lvg3401]

Hi Guys

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

Simple table in the format:

Ticker___ Date___ Close
JJB.L _08/04/2009 13.5
JJB.L _07/04/2009 12.75
JJB.L _06/04/2009 11
JJB.L _03/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)

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

Any help is much appreciated!

Thanks

Rob

 

[rainman89]

Check out this link and see if it gets you moving

http://www.quackit.com/sql/tutorial/sql_top.cfm

Im assuming you are going to go by date??? AND I hope that your column name is not date, but tickerdate or something along those lines as date is a reserved word in access...

 

[lvg3401]

Thanks rainman i'll have a look through!

 

[ajetrumpet]

i personally think you'll need to stack two queries here, but rainman is a genius, so i'm sure you're in good hands. =)

 

[lvg3401]

Check out this link and see if it gets you moving

http://www.quackit.com/sql/tutorial/sql_top.cfm

Im assuming you are going to go by date??? AND I hope that your column name is not date, but tickerdate or something along those lines as date is a reserved word in access...

Yeah sorted by date, latest first. *Bow's head in shame* yes it was called Date but its now TickerDate

Aje - I think you're right but the solution still eludes me

 

[rainman89]

cant you do....

SELECT [B]TOP 200 [/B]* FROM tickertable where ticker = "[FONT=Arial][SIZE=2]JJB.L"[/SIZE][/FONT]
[B]ORDER BY date DESC[/B]

I havent tried it though... stack away!!

 

[pbaldy]

I've used method 1 numerous times:

http://support.microsoft.com/kb/153747/en-us

 

[ajetrumpet]

nice search Paul! I have never seen that article before. lol

 

[lvg3401]

cant you do....

SELECT [B]TOP 200 [/B]* FROM tickertable where ticker = "[FONT=Arial][SIZE=2]JJB.L"[/SIZE][/FONT]
[B]ORDER BY date DESC[/B]

I havent tried it though... stack away!!

But wont that just bring back the last 200 records for JJB? I need it to bring back the last 200 records for each Ticker (100+)

The obviously stupid approach could be to create one of those for each Ticker and Union them together, but I dont feel that would be the most effective method of solving the problem...

 

[lvg3401]

I've used method 1 numerous times:

http://support.microsoft.com/kb/153747/en-us

Thanks for the link Paul, cant try it now until im back at work tomorrow..

 

[rainman89]

The obviously stupid approach ...

Easy there.. Just trying to help you

 

[ajetrumpet]

Easy there.. Just trying to help you

i think i can second this motion. this is a free forum. right Ray?

 

[lvg3401]

Easy there.. Just trying to help you

Sorry mate, I really wasn't directing that at you at all..! I thought you had just miss-understood what I was asking for. I honestly didn't mean to offend in any way. I actually meant that the only way that I could solve the problem was with the really stupid and in effective method of union query's which I have done in the past

I appreciate all assistance given and again am sorry if I caused offence

 

[rainman89]

I thought you had just miss-understood what I was asking for.

I did.. and apology accepted!

 

[ajetrumpet]

adam will now initiate a group hug...

 

[rainman89]

i think i can second this motion. this is a free forum. right Ray?

Sometimes it costs brain cells???

 

[lvg3401]

Update:

Ok, using this site : http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

And using this example:

select type, variety, price
from fruits
where (
select count(*) from fruits as f
where f.type = fruits.type and f.price < fruits.price
) <= 2;

I now have this:

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 < [Stock Data BK].TickerDate
))<=2));

Which is a BIG step closer than any of my previous attempts.

Unfortunately this is not quite there... It does group the Ticker symbols and currently displays 3 for each, but it is choosing the earliest 3 records and not the last 3 records which is what im after. Another problem is speed, it takes a few seconds to run it on a data source of 18k records, and my main table has 400k, so its a bit slow to say the least.

Will post any updates as and when I have them

 

[lvg3401]

I believe I just need to add an Order by some where in the above code to get the latest records, just not figured out quite where yet...

 

[lvg3401]

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)<[minofindex]+200))
ORDER BY Quotes.Date DESC;

Runs pretty fast so im happy