Re: about selecting data from a table

Leader" <sohelcsc_at_yahoo.com> wrote

> i am facing a simple problem that is, suppose i want to retrieve data
> from a table but not all data i just want to give from which row to
> which row. suppose there are 10000 row in my table but i want to
> retrieve from 2500 to 7500. what will be best solution of this.

Let me borrow Sybrand's soapbox for a moment - The best solution is to, after correctly understanding the business requirement, design and implement the table accordingly.

Failing that..

Using the "WHERE rownum" clause will be of little help I think as although you can do "WHERE rownum < 1001" (to get the first 1000 rows), you can not use something like a "WHERE rownum BETWEEN 500 AND 599" (returns no rows).

The quick and dirty method that comes to mind is to alter the table, add a numeric column, create a sequence, and then do an update on that table, setting this new column equal to sequence nextval. After which you create an insert trigger to automatically populate this column for future additions to the table.

Remember though that you are guaranteed to have gaps in the numbering sequence (Thomas posted on this exact subject a few days ago) - unless of course you always update the entire table with a fresh sequence to ensure no gaps in the numbers after any changes (inserts/deletes/updates) to the table.

--
Billy