Query Last 60

[Torben Villumsen]

I have a large table with approx. 1000 different securities with daily closing pricess for approx. 5 years. The Securities are sorted by name and then by date so that the latest date is the last one.

I need a query that can give me the last 60 days closing prices for each security. Therefore the new table will have 60,000 daily closing prices (1000 securities * 60 daily closing prices - latest).

My table is sorted in such a way that the latest date is at the bottom of the table. Please see below:

Share-A 15/05/09 101.50
Share-A 16/05/09 102.50
.
.
Share-A 03/07/11 110.25
Stock B 15/05/09 111.25
.
.
Stock B 03/07/11 116.25

Hope someone can help.

 

[jdraw]

Have you considered sorting the data descending, and get the top 60?

 

[Torben Villumsen]

Thanks for your reply. The data is sorted by name and then by date. The issues is that I have all my securities in one large table. But I want to create a new table but only with the latest 60 days closing prices for each stock. If I just take top 60 (in this case it should be bottom 60) I will get data for one security, but I want a new large table with all 1000 securities. That is to say 60 rows for each security containing the latest 60 days closing prices.

 

[spikepl]

then select the top 60,000

 

[jdraw]

If they are ordered by Date within Name,
that is
Security1 LatestDate
Security1 LatestDate - 1
..
Security1 LatestDate-60
...
Security1 LatestDate -800
Security2 LatestDate
Security2 LatestDate - 2
etc

Then look at this query from Allen Browne (Top N(60) per group(SecurityName)

http://allenbrowne.com/subquery-01.html#TopN

 

[Torben Villumsen]

Thanks for your replies. Jdrew your last post seems like the right solution. I will test that solution. Thanks.