Re: Returning Row 20 to 29

On Wed, 18 Feb 1998 16:04:42 -0500, "Huy V. Le" <huy_le_at_cs.concordia.ca> wrote:

>Hi Everyone,
>
>After what I have read from the newsgroup, You can limit the number of
>row return by using "rownum" (Assuming that you don't group)
>
>But let say you want to do a second query that will return row 20 to 29.
>
>Is it possible?

Not using rownum. It's because rows eliminated by constraints are not counted in rownum.

Say you wanted to do something like this:

select foo_id from foo where rownum between 20 and 29

So you get the first row, it would be rownum 1, so you throw it out. So you get the second row, it would be rownum 1, so you throw it out. And so forth. Oops, you just threw out the whole result set. Nice going. :-)

In other words, rownum is only useful in the form rownum <= x, or perhaps rownum = 1.

Workaround: Create a temp table or snapshot based on your query result and add a column [call it x_seq] that imposes order with a sequence or some other method. Now you can use a query like:

select foo_id from foo where x_seq between 20 and 29  

Yay.

/cpk

/cpk Received on Thu Feb 19 1998 - 00:00:00 CST