Limiting Database

[A1ien51]

Okay I want to make a top ten list for scores for my game. I am able to write to a database and get its values, but I am not sure how to limit the record set to the top 10 scores. I do not want to keep all of the scores that are lower then the top 10 since I do not want a really big mdb file.

Does anyone know of a tutorial to do this or have the code.

Thanks
A1ien51

[dominicall]

This should work

To select the top 10, use the following in the select statementSELECT TOP 10 FROM....
Then to delete the scores below the top 10 I would guess prob the best thing is to do something like
Dim arrURNs
While Not rsScores.EOF
arrURNs = arrURNs & rsScores("urn") & ","
rsScores.MoveNext
Wend

arrURNS = "(" & (Left(arrURNs),(Len(arrURNs)-1)) & ")"

Dim strSQL

strSQL = "DELETE tbl_Scores WHERE tbl_Scores.URN NOT IN arrURNs"If the codes not quite right, that's the principle - you're need to delete from the table where the URN is not in the top 10.

Let me know how you get on.

dominicall

[A1ien51]

I will play with it and see what I can come up with, just to let you know this is what I am adding this too, my newest game (still in the works, my testers are ripping it apart!)......

http://www10.brinkster.com/a1ien51/Card/Solitaire13.htm

[raf]

I think it should be done in one db-call.

If you use TOP, remember you need to have an order by clause since it takes the top 10 records from an ordered view on the table.
First problem: if the 10'e highest score is the same as the 11'e one, Access will not cut of after 10 records. All records with the same score will also be selected.

If you first run a select, and get the '10' records, and then start a loop to delete the records that are not in the array (the 10 records), then you'll soner or later get into problems. What if a user submits his result during this operation? The highest scoring user ever! He'll not be in the array, and his record will be deleted. Locking the table during the whole operation will slow down things to much.

So i think you'de better of using a subquery

strSQL = "DELETE from tbl_Scores WHERE scoreID NOT IN (SELECT TOP 10 scoreID FROM tbl_Scores ORDER BY score desc)"

In fact, i don't quite understand the situation. Either you register all scores and build a top 10 on that (just create a descending index on the scorevariabel for superfast selecting), or, you only register the score if it is a top10 score (else you don't register it at all).
In fact, the propper thing to do this is probably save all scores + save the top10 scores in an array in an applicationvariable.

[A1ien51]

I was going to limit it on the client side by not submitting a score that is not above the range. I just do not want a database of 10000000 games. I am very bad at coding in ASP, so I am trying to figure out the best way to do it.

If this were JavaScript, I would have no trouble taking this information from an 2D array and writing the code for it.

A1ien51

[whammy]

raf's approach should work... also, you should be able to compare their score with a value received from the server, and not allow them to post a score unless it's in the top 10...