Re: ROWNUM Query for multi table joins

Ritu wrote:

> I am looking for selectively retrieving reults from a query for a
> multi table join iteratively like
>
> 1) ROWNUM BETWEEN 25 AND 50
> 2) ROWNUM BETWEEN 50 AND 100
>
> And so on. I found this query from a newsgroup thread
>
> select a.rn, a.user, a.email from
> (select rownum rn, user, email from clients) a
> where a.rn between 40 and 100
>
> which works fine for a single table.
>
> Any ideas of how to map / translate it to a multitable join. The Join
> statement will be something (pseudo code )like
>
> SELECT A.Name, B.Address
> FROM A , B
> WHERE A.ID = B.A_ID
> AND A.ID = 124
> ORDER BY A.Name Asc
>
> Let us assume that this query fetches 500 records.. how can we do it
> iteratively in ranges of 25 records.
>
> Any help will be greatly appreciated.
>
> Thanks
> RR

The same methodology works exactly the same way. Just select rownum as part of the in-line view and then let the outer query use the rownum value to create the select group of records.

The negative with doing this type of processing is that every single time you run the query you are essentially rerunning the entire query from scratch. This is not exactly a performance enhancing move with large tables or lots of queries.

Daniel Morgan Received on Mon May 06 2002 - 10:25:22 CDT