convert autoID numbers from recordset 1

[cr84net2]

I am working on a project where I am laying out a list of players for each team in a given league. The list looks like this (The draft order numbers are autoID):

The Brawlers

Wgt    Name        Draft Order  Needed
125    Fred           1342         9
133    Dave           1301         2
141    Mike           1304         3
149    Phil           1325         6
157    Steve          1343         10
165    Bob            1312         4
174    Albert         1313         5
184    Robert         1327         7
197    Frank          1340         8
285    Sid            1300         1

I don't have a problem laying out the lists for each team.What I need is to convert the autoID numbers to 1-10 for each team based on their order. Basically I need the draft order column to look like the Needed column in the table above.

I am using ASP and MS SQL DB and using a loop to layout the teams etc. Any ideas on how to convert these would be greatly apreciated.

I am already converting the weight classes from 1-10 to the appropriate weight classes with a function outside of the loop. But i can't get my head around how to do this with unknown autoid numbers.

I am ordering by Team, weightclass, then the autoID

Thanks!

 

[BigRed1212]

I may not understand what you want, but if all you need to do is present the data in draft order, could you append your data select statement with "order by draft asc" so that the recordset is coming to you in the right order and then when you write the data in your loop, start a counter at one, increment the counter each time through the loop, and write the counter value? Or are you already sorting on something else?

 

[cr84net2]

Thanks BigRed1212, I wish I could but I am ordering or sorting by Team, weightclass, then the autoID. The autoID field is also the draft order however several people could be drafting at once so I will have no idea what those ID numbers will be, except that the lowest autoID number is the first draft pick etc.

Thanks again,

 

[BigRed1212]

I'm not clear on what you are doing or what you want.

 

[tsuji]

Suppose you have the table called Team. You can use this as the sql. ("needed" is the field name. Change it to suit your desired name.)
[tt]
ssql="select Wgt,Name,(select count(*) from Team t2 where t2.[Draft Order]<=t.[Draft Order]) as needed from Team t order by Wgt asc"
[/tt]

 

[cr84net2]

Thanks tsuji. I appreciate your help. I will give that a try.

 

[cr84net2]

tsuji I needed to use a join, does this look right to you? I am getting an "Incorrect syntax near the keyword 'From" error.

fldSql= "SELECT weightclass, wrestlername,leagueplayer from lteam t2"
fldSql= fldSql & " INNER JOIN leagueplayers t1 ON [user] = playerID"
fldSql= fldSql & ",(select count(*)From t2 t1 where t2.[ID]<=t.[ID] AND [user] = playerID)As do From lteam t" 
fldSql= fldSql & " Where t.ltID=" & lteamID & "ORDER BY [user],weightclass"

When I added the inner select before the join it obviously listed the draft order as a number from the league not the team. I tryed to keep this simple but probably should have explained even more. I really appreciate your help with this.

 

[tsuji]

As it is not on the surface clear which column belongs to which table, I try where it becomes necessary to be explicit with playerID belonging to leagueplayers table, and user belonging to lteam. If not, just reverse them.
[tt]
fldSql="SELECT weightclass, wrestlername, leagueplayer, " & _
"(SELECT COUNT(*) FROM lteam AS t2 WHERE t2.ltID=t.ltID" & _
" AND t2.[ID]<=t.[ID] AND g.playerID=t2.[user]) AS do " & _
"FROM lteam AS t, leagueplayers AS g WHERE t.ltID=" & lteamID & _
" AND g.playerID=t.[user] ORDER BY t.[user], t.weightclass;"
[/tt]

 

[cr84net2]

tsuji, Excellent, that worked like a charm. I really appreciate your help with this it was drinving me crazy.

Thanks again!!!