ok here's my problem:

i need to write some sql code for a game ranking.
there are pretty many ppl in it so it has to be fast

tried to move within ADO to the startrow and then select as many as i need per page but this is taking too long

rs.Move pagesize*(page-1)+1
for i=1 to pagesize
.
.
.

so is there any way within TSQL to determine the position of a row from within an sql statment

e.g. select @@row_position, * from ranking ?

or any similar solution you may think of? of course i could use a temporary table but this would be slow again, as far as i'm concearned

my current approach is fast but doesn't work with equal ranking scores:

select top pagesize from ranking where score > (select top pagesize*(page-1)+1 scores from ranking)

help would be appriciated,

sincerly yours

thomas K

For what i see is why not returning the record u wish to take by specify the distinct value using where condition?

: ok here's my problem:
:
: i need to write some sql code for a game ranking.
: there are pretty many ppl in it so it has to be fast
:
: tried to move within ADO to the startrow and then select as many as i need per page but this is taking too long
:
: rs.Move pagesize*(page-1)+1
: for i=1 to pagesize
: .
: .
: .
:
: so is there any way within TSQL to determine the position of a row from within an sql statment
:
: e.g. select @@row_position, * from ranking ?
:
: or any similar solution you may think of? of course i could use a temporary table but this would be slow again, as far as i'm concearned
:
: my current approach is fast but doesn't work with equal ranking scores:
:
: select top pagesize from ranking where score > (select top pagesize*(page-1)+1 scores from ranking)
:
: help would be appriciated,
:
: sincerly yours
:
: thomas K
:

Neo Gigs
http://communities.msn.com.my/VisualBasicwithNeo

: For what i see is why not returning the record u wish to take by specify the distinct value using where condition?
:

i'm sorry but i don't get what you mean?
i have to generate the position on the fly, they are not set in some pos field.
so i have to get part of the database with the positions of this selection within the whole table

Let me get this clear, what basically u need is to get a position of 1 record in the table?

: : For what i see is why not returning the record u wish to take by specify the distinct value using where condition?
: :
:
: i'm sorry but i don't get what you mean?
: i have to generate the position on the fly, they are not set in some pos field.
: so i have to get part of the database with the positions of this selection within the whole table
:

Neo Gigs
http://communities.msn.com.my/VisualBasicwithNeo

: Let me get this clear, what basically u need is to get a position of 1 record in the table?

exactly, but if it is possible without retrieving the whole table, cause due to the number of ppl in this ranking this would make the ranking really slow

Why u use the position of the records as the ranking and not use a field to store the current ranking?

: : Let me get this clear, what basically u need is to get a position of 1 record in the table?
:
: exactly, but if it is possible without retrieving the whole table, cause due to the number of ppl in this ranking this would make the ranking really slow
:
:

Neo Gigs
http://communities.msn.com.my/VisualBasicwithNeo

: Why u use the position of the records as the ranking and not use a field to store the current ranking?

that's what i wanted to know: how do i get the position within the whole table without retrieving the whole table, is there an index os something alike ?

This is very tedious programming, then u have no way to find out becoz this does not comply with normalization.

: : Why u use the position of the records as the ranking and not use a field to store the current ranking?
:
: that's what i wanted to know: how do i get the position within the whole table without retrieving the whole table, is there an index os something alike ?
:
:

Neo Gigs
http://communities.msn.com.my/VisualBasicwithNeo

: This is very tedious programming, then u have no way to find out becoz this does not comply with normalization.


thats what i thought, but you never know if there aint another way.
thx 4 bothering

cya
thomas

There is no way u can achieve this...against normalisation rules...change it to what i propose to you.

: : This is very tedious programming, then u have no way to find out becoz this does not comply with normalization.
:
:
: thats what i thought, but you never know if there aint another way.
: thx 4 bothering
:
: cya
: thomas
:

Neo Gigs
http://communities.msn.com.my/VisualBasicwithNeo

You can return a row number in a query using T-SQL but it's a little cumbersome. You can read an article here (http://www.sqlteam.com/item.asp?ItemID=1491). It's also not the best database design approach.

: ok here's my problem:
:
: i need to write some sql code for a game ranking.
: there are pretty many ppl in it so it has to be fast
:
: tried to move within ADO to the startrow and then select as many as i need per page but this is taking too long
:
: rs.Move pagesize*(page-1)+1
: for i=1 to pagesize
: .
: .
: .
:
: so is there any way within TSQL to determine the position of a row from within an sql statment
:
: e.g. select @@row_position, * from ranking ?
:
: or any similar solution you may think of? of course i could use a temporary table but this would be slow again, as far as i'm concearned
:
: my current approach is fast but doesn't work with equal ranking scores:
:
: select top pagesize from ranking where score > (select top pagesize*(page-1)+1 scores from ranking)
:
: help would be appriciated,
:
: sincerly yours
:
: thomas K
:

I hate to tell you this but in MSSQL (at least 7.0, not familiar with 2000 yet) there is no such thing as a row number. You could simulate one by using an identity (autoincrementing) column in your table.

Hope this helps.


: : For what i see is why not returning the record u wish to take by specify the distinct value using where condition?
: :
:
: i'm sorry but i don't get what you mean?
: i have to generate the position on the fly, they are not set in some pos field.
: so i have to get part of the database with the positions of this selection within the whole table
:

Normally i will do something like below to get the latest increment value if the particular column is a unique key:

SELECT TOP 1 FieldA FROM Table1 ORDER BY FieldA DESC

this will get the largest value, then increase it by 1 inside the application. Before update or insertion, do a SELECT statement to find out whether the same number has been inserted or updated by other users as well.

: I hate to tell you this but in MSSQL (at least 7.0, not familiar with 2000 yet) there is no such thing as a row number. You could simulate one by using an identity (autoincrementing) column in your table.
:
: Hope this helps.
:
:
: : : For what i see is why not returning the record u wish to take by specify the distinct value using where condition?
: : :
: :
: : i'm sorry but i don't get what you mean?
: : i have to generate the position on the fly, they are not set in some pos field.
: : so i have to get part of the database with the positions of this selection within the whole table
: :
:
:

Neo Gigs
http://communities.msn.com.my/VisualBasicwithNeo