Re: Best way to retrieve rows

Hey XU

If there is another column available in the table that is unique, and orderable, then use that column and not the rownum. Make sure you index the column and remember the 'page size' in the querying app. like "im on record 50 and i want 10 more.' then you can use a select statement like:

select all_my_rowdata, my_seq_col
where my_seq_col > 50
and rownum <= page_size

if its indexed, then the retrieval should be consistent no matter where you are in the file.

good luck
randy

XU wrote:

> Hi, as we know that "select * from table_one where rownum <=10" will
> retrieve the first 10 rows of table_one, and "...where rownum <=20" will
> return the first 20 rows. But how about that I want to get the rows from
> 11 to 20 of table_one? Is it possible to retrieve an arbitrary portion of
> a table using rownum (NOT just first 10, first 100, etc., rows)? One
> possible solution I came up with is using "minus". For example, for the
> 11th to 20th rows of table_one:
>
> select * from table_one where rownum <=20
> minus
> select * from table_one where rownum <=10
>
> It seems to work but maybe there is a better solution? In general, this is
> a question of how to "page" through a large table - allow users to see the
> first 100 records, and then 101-200, 201-300, etc. Is rowid, sequence, ...
> going to be useful in this regard? Thanks.
>
> Robert XU
Received on Sat Jun 05 1999 - 17:56:32 CDT