Database Discussion - how to select 2 up and 2 down

Print Page | Close Window

how to select 2 up and 2 down

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=12008
Printed Date: 30 March 2026 at 4:18am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: how to select 2 up and 2 down

Posted By: asp what that??
Subject: how to select 2 up and 2 down
Date Posted: 01 October 2004 at 12:57pm

what i am trying to do is slect 2 up and 2 down from a table

say this is the info in my table

1   doug   $111
2   doug   $111
3   doug   $111
4   doug   $111
5   doug   $111
6   doug   $111
7   doug   $111

what i want to do select the records that are 2 up and to down from the red

strSQL = "SELECT TOP 6 * FROM tblAuthor WHERE Author_ID <> all ( SELECT Top 5 Author_ID FROM tblAuthor Order By Cash desc)Order By Cash desc;"

this will select the top 5 and take them out and will leave me with the top 6 of what is left.

what i need to figure out is how to tell me what the number is for the record so i can changed the TOP (x) numbers so that i can get the top 2 and bottom 2 from a member...any ideas?

Replies:

Posted By: theSCIENTIST
Date Posted: 03 October 2004 at 7:31pm

Humm, I think what you need is this:

rsRecSet.RecordCount
This gives you the number of records your query returned.

rsRecSet.AbsolutePosition
This tells you when you are looping through the recorset which record is being processed at that moment, with this you can do what's needed.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: asp what that??
Date Posted: 03 October 2004 at 7:54pm

let me try that..TY

Posted By: asp what that??
Date Posted: 03 October 2004 at 7:58pm

could u give me a small example of how this woudl work

Posted By: theSCIENTIST
Date Posted: 03 October 2004 at 10:05pm

I don't understand very well what you are trying to accomplish, please elaborate.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: asp what that??
Date Posted: 03 October 2004 at 10:40pm

OK...this is what my db looks like

what i need to do is select the top 5 records order by cash

and that will be the top 5 players and that will be in one table on the site

now what i need to do is.... say your ID is 29

what i want to do is select X about up and X about down we will just say 2 up and 2 down for a total of 5 for now

like this...

what is it is i am trying to do is have 2 tables on the site

the first table will be the top 5 players

the second table will be a group of 5 to 10 players that you fall in with....order by Cash..but you need to be the center of the second table...how that helps out a little bit

Posted By: Phat
Date Posted: 04 October 2004 at 6:27am

Use rs.Move 2 and then rs.move -2

Posted By: asp what that??
Date Posted: 04 October 2004 at 8:51am

could u show me a small example for me to work off from?

Posted By: theSCIENTIST
Date Posted: 04 October 2004 at 11:52am

Right, I see now, well, I can't think of any way to retrieve a bunch of records that are around a given record, I can only think at this time to retrieve all records, then sort them out one by one and get your 5 (inc. bill) like that, this is very inneficient method but that's all I can think of right now.

strSQL = "SELECT allFIELDS FROM someTBL"



rsRecSet.Open strSQL, strCon



Dim I, arrID(4), arrName(4), arrCash(4)



Do While Not rsRecSet.EOF

��If int(rsRecSet("ID")) = 29 Then

    rsRecSet.Move - 2

    For I = 0 To 4

      arrID(I) = rsRecSet("ID")

      arrName(I) = rsRecSet("Name")

      arrCash(I) = rsRecSet("Cash")

      rsRecSet.MoveNext

    Next

    Exit Do

��End If

��rsRecSet.MoveNext

Loop

This should do it, note you have to change things to match your data, also note, this was not tested and was just written as I went along with my code logic, but it should work, at least the principle should. Also not sure if ASP supports multi-column arrays (someARR(4, 4, 4)) if so you could use that.

Retrieve your data with this:

For I = 0 To 4

��Response.Write(arrID(I) & " " & arrName(I) & " " & arrCash(I))

Next

The above should return your 5 records.

Let me know how it went.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com

Posted By: asp what that??
Date Posted: 04 October 2004 at 1:54pm

ty...i will try it as soon as i get off work tonite