Only considering the last 10 records for each ID

[dasphatman]

I'm having this golfclub where i want to create my own "world ranking" by getting the average of each players points, but I only want the script to consider the latest 10 records for each player. I have no problem with the first part as the script below does.

<%
Application("Connectionstring") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & _
Server.MapPath("db/my_db1.mdb")
Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT average, playerlist.Name FROM"
strSQL = strSQL & " (SELECT AVG(ranking.Point + ranking.Xtra_Point) AS average, playerlist.Name FROM"
strSQL = strSQL & " ranking INNER JOIN playerlist ON playerlist.PlayerID = ranking.PlayerID"
strSQL = strSQL & " GROUP BY playerlist.Name)"
strSQL = strSQL & " order by average desc"
rs.Open strSQL, Application("Connectionstring")
WHILE NOT rs.eof
%>
<td width="50%"><%=rs("Name")%></td>
<td width="50%"><div align="right"><%Response.Write FormatNumber(rs("average"),2)%> Point</div></td>
</tr>
<%
rs.Movenext
WEND
%>

Please help. It's driving me CRAZY....

 

[bhuninghake]

Could you not simply

SELECT top 10 average, playerlist.Name FROM

you stated you wanted the latest 10 records for each player, if so you will need to order by a date field, but if you want the lowest scores I'm thinking if you add the top 10 that might do the trick.

 

[dasphatman]

The SELECT TOP 10 does not do the trick, cause that will only get me the top 10 players of the Ranking l0 players of the Ranking tabel with the average of all their records.
I think i first need to get the average for each player using the SELECT TOP 10 statement ORDER BY Date, and then some sort of code that wil look at the players after the first code and then tell me which player who has the best average. I hope you can understand my problem, cause its making me crazy.... PLEASE HELP

 

[DotNetGnat]

ok let me give you a sample query for the same scenario...but it is for only one table...try to modify it to suit to your case...

SELECT t1.PlayerId,t1.PlayerName, t1.Average
FROM [MyTable] t1
WHERE t1.Average In (select top 10 t2.Average from [MyTable] t2 where t2.PlayerName = t1.PlayerName ORDER BY t2.Average Desc);

What we are doing here is---we are creating aliases t1 and t2 of the same table [Mytable] and getting the top 10 averages of each player....

you can change t2.PlayerName=t1.PlayerName criteria to t2.PlayerId=t1.PlayerId depending on your table structure....

Hope that helps

-DNG