Re: Rownum

Naguib Younes wrote:

> Does anybody know how to return just a small set of data, ex record 20 to 30
> in a record set, in a select statement, i used rownum, but i soon as you do
> an order by, it stops working, i used hints, but it stop working when the
> SQL become complicated, i'm out of ideas how to fixe this,

There have been a number of responses to this question. I have a similar problem I am trying to get an answer for.

First a question: The solution provided below:

> try this (8i):
 

> select * from (
> select rownum rank, t1.*
> from your_table_name t1
> order by your_column(s)
> )
> where rank between 20 and 30

I have not played with 8i yet but I believe this is just ordering the records with rownum between 20 and 30 or is this doing something more?

My problem - consider the following:

A Web based application which provides the end user a means to retrieve, report and manipulate a database. The application provides reports of the database via html screens. As the database grows, we can have many thousands of entries.

We have found many techniques to limit the size of queries. For example, we provide fields for limiting searches; we do not allow certain views of the data via the web. But whatever the technique, we still have no good way of showing blocks of data from the database. Wherever you look on the web, you see what I am looking for:

At www.lycos.com no matter how large the number of entries returned with your search, you get only the top 10 or 20 entries and links to more data. You never get the entire list of matches back at once. This is what I want - getting blocks of data from the database ordered in anyway I choose.

Thanks.

-- 
Bill Jenkins,                   wcn_at_caesun.msd.ray.com