Re: Q: returning rows between two positions in a large query

Minor addition:
Rownum reflects the order in which rows were retrieved, not necessarily how they were written. Depending on what buffers are in the SGA, rownum may change from one query to another. It may not, but probably will when you need it to the most. Consider it unreliable for sequencing.

In article <376fa324.0_at_145.227.194.253>,   "Mark G" <mgumbs_at_nospam.hotmail.com> wrote:
> The suggested solutions are fine for simple queries but once you
start doing
> sorts on the tables, as the original thread suggested, the rownum
method may
> return unexpected results. Using rownum will return the stated
amount of
> records but ONLY in the order which is stored in a table.
>
> e.g You couldn't do query to search on the top 2000 items, sort by
> occurence and then find all items between position 100 and 200.
>
> Combining all this, why don't you do your initial query, save it to a
temp
> table in the order you want and then use the rownum method stated
below
> based on the sorted temp table?
>
> Mark
>
> shiling_at_math.wayne.edu wrote in message <7ko40t$959
$1_at_nnrp1.deja.com>...
> >The following example selects records between #3-#4.
> >
> >SQL> select * from t
> > 2 ;
> >
> >TDATE
> >---------
> >09-JAN-99
> >09-JAN-98
> >09-JAN-97
> >09-JAN-96
> >09-JAN-95
> >09-JAN-94
> >
> >6 rows selected.
> >
> >SQL> select * from t where rownum<=4
> > 2 minus
> > 3 select * from t where rownum<=2;
> >
> >TDATE
> >---------
> >09-JAN-96
> >09-JAN-97
> >
> >SQL>
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 22 1999 - 12:40:46 CDT