Finding top values

[winston1984]

I was going to do this using an SQL query but decided better of it.

I have a database which cannot be changed, structure:

Name 1 2 3 4 5 6 7 8 9 10 Total
Phil 3 4 5 2 5 0 4 3 2 1 29

I am happy to create the total dynamically on the page, however, this is a league table of scores, only the best 8 scores count. Therefore, I want it to be displayed:

Name 1 2 3 4 5 6 7 8 9 10 Total
Phil 3 4 5 2 5 0 4 3 2 0 28

How can I take the values from the database, find the top 8 and then display them. Also the match number must be kept so that I can put the correct scores against the correct match.

Any ideas?

 

[Tarwn]

No mater what I think you will need to do some sort of comparison. Best bet would probably be to find the two lowest values in the set. Maybe something like

'pretending the scores are in fields 2-11 so I can use numeric loop
Dim i, low1, low2
low1 = 10000
low2 = 10000
For i = 2 to 11
   'if the current value in low1 is smaller
   If low1 < cInt(rs(i)) Then
      'if the value curently in low1 is smaller than low2
      If low2 < low1 Then
         'move low1's value to low 2, give low1 the new value
         low2 = low1
         low1 = cInt(rs(i))
      Else
         'otherwise just set low 1 - this case shouldn't happen
         low1 = cInt(rs(i))
      End If
   'otherwise check against low2
   ElseIf low2 < rs(i) Then
      'put value in for low2
      low2 = cInt(rs(i))
   End If
Next

Then when you output you just check the value your outputting against those two low values.

Another option would be to somehow sort the values. Rather than get into all kinds of sorts I'm going to only show you a simple one that probably isn't very efficient:

Dim arr(9), i, j, pos
'initialize array
For j = 0 to 9
   arr(j) = -1
Next
'for each of the scores in the rs
For i = 2 to 11
   pos = -1
   'for each index in the array
   For j = 0 to 9
      'if the array at this index is still -1, replace it
      If arr(j) = -1 Then
         arr(j) = cInt(rs(i))
         Exit For
      'otherwise if the array value is bigger than the curent score
      ElseIf arr(j) > cInt(rs(i)) Then
         'save this position as the correct one for this score
         pos = j
         Exit For
      End If   
   Next
   'if an occupied position was found for the score
   If pos > -1 Then
      'loop backwards through the array, shifting all the existing values to that position to the right by one to make space for the incoming score
      For j = 8 to pos
         arr(j+1) = arr(j)
      Next
      'place the incoming score in the position now that it's previous occupant has been shifted
      arr(pos) = cInt(rs(i))
   End If
Next

This time we are trying to fill an ordered array. When you go to output the values you would compare against the first two entries in the array (those being the lowest values). If the value your printing matches one of those two, you don't print it or add it to your total.

Anyway, tere are a couple methods and there are countless more for ordering and such. basically since your database isn't setup to do the work for you, it leaves you in the positin to do the work in a less efficient manner. Hopefully this has helped,

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/