Get top 5 1

[Kendel]

Hi,

I'm looping through recordset to build a combobox.

Do While Not RS.EOF
Response.write "<option value="& RS("amount")& ">="& RS("amount")& "</option>"
RS.MoveNext
Loop

Now, how can I get the 5 biggest number from the above box?

Many Thanks.

 

[onpnt]

can you go with the TOP clause to jsut return the TOP 5 in the DB. or do you need to list them all initially

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[Kendel]

I need to list them all first then get the top 5. Thanks.

 

[onpnt]

I would suggest the process may be quicker if you return the TOP 5 and the total amount. You can do that with a subquery in the execute

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[DreXor]

you can also dim topfive(4) and as you go through basically do a bubble sort/kickout lowest value and at the end the array will contain the 5 values you want

 

[Kendel]

DreXor, can you help me with the bubble sort? Thanks.

 

[vongrunt]

Write SELECT/OPTION tags, then re-use the same recordset to find top 5. Something like:

RS.Sort = "amount desc"
'RS.moveFirst
For i = 1 To 5
	If RS.Eof Then Exit For
	
	' retrieve RS("amount") blah blah...
	RS.moveNext	
Next

If you want top 5 without duplicates, code is somewhat different.

 

[DreXor]

google up VB6 bubble sort, there's hoards upon hoards of bubble sort examples/tutorials

easiest is a comparitive...

here's a small sample to possible help :

<%
response.expires = 0
dim topfive(4)
values = split("1,2,3,4,5,6,7,8,9",",")

for each value in values
  topfivevals(value)
next

Function topfivevals(value)
  for i=0 to ubound(topfive)
    If value > topfive(i) then
      for j=ubound(topfive) to i step -1
        If j-1 => 0 then 
          topfive(j) = topfive(j-1)
        end if
      next
      topfive(i) = value
      exit for
    End If
  Next
End Function

Function topfiveout()
  response.write "----------------------------------<br>" & VbCrLf
  for z=0 to ubound(topfive)
    response.write z & "|" & topfive(z) & "|<br>" & VbCrLf
  next
  response.write "----------------------------------<br>" & VbCrLf
End Function
%>

 

[Kendel]

Thanks for all the inputs.

RS.Sort will work for me.

-kendel

 

[YNOTU]

Kendel, it's very nice to see you finally came around awarding stars to those that kindly help you.

Way to go.

Keep Tek-Tips strong..

 

[onpnt]

I wouldn't recommend the .sort

OLEDB mostly does not support this method for one and the point the dbms can do the work at this point with a simple "ORDER BY"

Just a suggestion



___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[vongrunt]

AFAIK I never had problems with .sort()... as long as CursorLocation/CursorType were properly set.

For small data sets (typically needed to fill listboxes) it won't hurt, plus there is one DB query less.

 

[onpnt]

That's cool vongrunt

I was just throwing my own experiences and readings out there. I am a firm believer in using SQL in what it is meant to be used for far before considering working through the RS. Personally have never been a fan of the performance of ADO with ASP. I've been largly impressed with ADO.NET over the previous ver. but we're talking two completely different platforms comparing those two.

for everyone else -- vongrunt made a good point. The sort (and or filter) will not work without your CursorLocation set to adUseClient



___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[DreXor]

another method would be to fire out another RS statement for

Select top 5 whatever from table where conditions order by [amount] desc

 

[Kendel]

I build the dropdown box based on a big existing SP and I don't have permisson the change this SP. That's why I couldn't use "top 5" and "order by [amount]"

I use RS.Sort, as vongrunt suggested, to re-sort the recordset and then get the top from there.

Thank you all.