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

Open in new window

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

Open in new window

Any help would be much appreciated!

Éric Moreau

don't you need to do a sub-select? something like this (sorry removed most VB code from it but you should get the idea):

select *
from (
   SELECT Row_Number() OVER (ORDER BY FnlScnds) AS IndRsltsID_DETER 
   FROM IndRslts 
   WHERE RacesID = " & iThisRace     sqlx = sqlx & " AND Fnlscnds > 0 
) AS A
WHERE Bib = " & iMyBib

Open in new window

Andrei Fomitchev

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.

Éric Moreau

any success?

DBAduck - Ben Miller

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.

Bob Schneider

ASKER

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

Bob Schneider

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!

ASKER CERTIFIED SOLUTION

DBAduck - Ben Miller

membership

This solution is only available to members.

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

Start Free Trial