Bob Schneider

asked on

# Getting a row number in sql server recordset

I need to get the row number that contains a specific value in a sql server recordset via vb6. This is what I am using but it appears to be generating a value of 1 each time. Note that what I am trying to get is the place a runner finishes in a race. Here is what I am using:

```
x = 0
Set rsx = New ADODB.Recordset
sqlx = "SELECT Row_Number() OVER (ORDER BY FnlScnds) AS IndRsltsID_DETER FROM IndRslts WHERE RacesID = " & iThisRace
sqlx = sqlx & " AND Fnlscnds > 0 AND Bib = " & iMyBib
rsx.Open sqlx, conn, 1, 2
x = rsx(0).Value
rsx.Close
Set rsx = Nothing
GetPlace = x
```

A brute force approach which would generate the desired value would be:

```
x = 0
Set rsx = New ADODB.Recordset
sqlx = "SELECT Bib FROM IndRslts WHERE RaceSrvrID = " & iThisRace & " AND FnlScnds > 0 ORDER BY FnlScnds"
rsx.Open sqlx, conn, 1, 2
Do While Not rsx.EOF
x = x + 1
If rsx(0).Value = iMyBib Then Exit Do
rsx.MoveNext
Loop
rsx.Close
Set rsx = Nothing
GetPlace = x
```

Any help would be much appreciated!

You can:

ALTER TABLE yourTable ADD Id INT Identity

After this your table will have the column Id: 1,2,3,4,...

I think it is better because the number of row (Id) will be saved with the row and stay the same forever.

Row_Number() will calculate numbers, but if you insert a row in the middle the row numbers will be changed.

ALTER TABLE yourTable ADD Id INT Identity

After this your table will have the column Id: 1,2,3,4,...

I think it is better because the number of row (Id) will be saved with the row and stay the same forever.

Row_Number() will calculate numbers, but if you insert a row in the middle the row numbers will be changed.

any success?

In your code you use "AND Bib = ...". Isn't that limiting the rowset to 1? I would use Eric's comment to get the Bib to be evaluated after the row_number() over statement.

ASKER

Sorry I haven’t responded sooner. Crazy busy with races. I will look these over tomorrow. Thanks so very much for the help!!!

ASKER

No success yet. I just want to take a query and find out how many rows it will return. I would like to execute the query because it is quicker than opening the recordset. As I understand it, rs.RecordCount only works if I open the recordset. The second approach I am using works fine but I wanted to speed it up. My first solution was not mine. It was given to me. I really don't have the skill to create those types of complex queries myself. I am fine leaving it as is if that is where my skill level leaves me.

As always, thanks so much for all of your help!

As always, thanks so much for all of your help!

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Open in new window