January 30, 2005 at 11:50 am
I am new to this board and I wanted to get some help with an SQL problem. With Benchmark Investing (BI), each year the number of stocks that qualify as BI picks varies. Some years there are 15 and some years there is only one. I have an Access database with the historical data from 1973-2004 and I can write the SQL to pick out all the BI stocks for each year. But I only want 4 from each year. How do I do this? Here is the SQL statement I have that picks out all the BI picks for each year:
SELECT * FROM [Dow History] WHERE [Price to Downside] IS NOT NULL AND [Price to Downside] < 0 AND [Earnings] IS NOT NULL AND [Earnings] >= .1 ORDER BY [Year] ASC, [Price to Downside] ASC
How do I alter this so that it will only pick 4 from each year? I tried the TOP operator (I used SELECT TOP 4 *) method but it only returned the top 4 from the first year (1973). I want it to return only up to 4 from every year (1973-2004). It's almost like I need some type of subquery but I am clueless about it. Any help would be appreciated.
Thanks,
Rick
January 30, 2005 at 4:51 pm
If I were writing the app, I would probably create a work table to hold the data and a subroutine that populates it year by year.
Sub top4()
Dim strSelect As String
Dim rs As DAO.Recordset
CurrentDb.Execute "delete * from top4"
Set rs = CurrentDb.OpenRecordset("select distinct [year] from [dow history] order by [year]", dbOpenSnapshot)
While Not rs.EOF
strSelect = "SELECT top 4 * FROM [Dow History] WHERE [Price to Downside] IS NOT NULL AND [Price to Downside] < 0 AND [Earnings] IS NOT NULL AND [Earnings] >= .1 AND [YEAR] = " & rs("Year") & " ORDER BY [Year] ASC, [Price to Downside] ASC"
Debug.Print strSelect
CurrentDb.Execute "insert into top4 " & strSelect
rs.MoveNext
Wend
Set rs = Nothing
End Sub
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply