Sybase ASA 9 has an optional "START AT" clause as part of its TOP
construct, which would do what you want. I don't know it manages it
internally, though, and I believe you have to reissue the query if you
want a different set (or starting point) of rows, so it might not be the
best choice to help you scroll through a million rows.


In article <yq5Hf.595417$ki.458465@pd7tw2no>, user@example.net says...
> For the sake of discussion let's say you have a result set with 1M records,
> you use an 'order by', and you want records 100-120.
>
> Forget for the moment *how* you would retrieve records 100-120 ...

....

>
> > Is there any kind of functionality, similar to a dynamic row ID, that I
> > could use to retrieve a subset of rows from the middle of a result set,
> > based simply on the position of the rows within the result set? For
> > example, I'd like to be able to say, "Give me only the 100th through
> > 120th rows of this result set". Obviously we use an order by in the
> > query to guarantee that the entire result set always looks like we
> > expect it to.
> >
> > I could just select all rows and let the code handle this, but this
> > forces the entire result set - sometimes containing millions of rows -
> > to come across the network, increasing traffic. I could limit the
> > result set by setting rowcount to my maximum value (in the above
> > example, 120) but depending on the query, table size, etc. this could
> > still result in much unnecessary network traffic.
> >
> > This could be accomplished with a temp table or a cursor with some
> > creativity, but I would like to have a dynamic solution... this option
> > would require specific solutions for each unique result set I am
> > parsing, and there are too many to go through the process of creating a
> > solution for each one.
> >
> > If sybase internally numbered the rows in the result set in a somehow
> > accessible way, this might be the solution, but I'm not aware of any
> > such functionality in Sybase.
> >
> > Most, if not all, of the tables involved do not have identity keys, so
> > I cannot use these to help. If I were to implement them, I would have
> > to guarantee no gaps in the values, which I could do, but these are
> > tables with very heavy activity and we're not willing to take the
> > performance hit of not pre-caching identity values.
> >
> > Any thoughts or ideas on how to accomplish this would be appreciated.
> > We're running ASE 12.5.3, ESD#1, Sun Solaris platform.
> >
> > Thanks in advance!
> >
> > -Mike
> >
>

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).