Re: Best way to retrieve rows

Mea culpa, mea culpa

Sorry folks I didn't read all of the way through before I slammed out a response to the wrong problem. Now I feel obligated to come up with a valid answer. So,

Lets modify the SELECT MINUS SELECT to use parameters instead of fixed values for the rownum and then either call it from or embed it in a PL/SQL loop. Given a page size of N we could then take the value of the lowest rownum in the current page and either add/subtract N in order to retrieve the next/prior page. For test purposes a SQL*Plus version could be:

            SELECT * FROM <table> WHERE rownum < (&1 + 10)
            MINUS
            SELECT * FROM <table> WHERE rownum < &1;

If this script is stored as x.sql

    @x 1 will display the first 10 rows

    @x 5 will display the rows 5 through 14

    @x 10 will display the rows 10 through 19

regards

Jerry Gitomer

Jerry Gitomer wrote in message <7j6jh1$ii3$1_at_autumn.news.rcn.net>...
>Hi,
>
> You might try:
>
> SELECT * FROM <table> WHERE rownum < 21
> MINUS
> SELECT * FROM <table> WHERE rownum < 11;
>
>regards
>
>Jerry Gitomer
>----------------------------------------------------------------
>
>XU wrote in message <7j3p8q$f6v_at_gap.cco.caltech.edu>...
>>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 Thu Jun 03 1999 - 14:22:05 CDT