Gene --

I usually use a sub-query to accomplish what you are trying to do, for
example:

select * from
(select rownum rn, de.* from some_table tbl order by tbl.id )
new_stuff
where
new_stuff.rn BETWEEN 40 AND 50;

This is very over-simplified, but shows the basic concept. You can
filter the sub-query with where clauses, andd things like order by
etc... The only thing you shouldn't do is refer to rownum in the where
clause of the sub-query, because that screws everything up :)

In practice, we usually have the application program (like perl) keep
track of high and low water marks, and we write stored procs that take
these as arguments and place them in the outer WHERE clause.

Mike

genegurevich wrote:

>I see. What would be a good way to have a query to return first only first
>10 rows , then another 10 etc?
>(this is not executed via SQL*Plus so I can't do pagesize)
>
>thank you
>
>Gene Gurevich
>Oracle Engineering
>224-405-4079
>
>
>
> "Kerber Andrew "
> <oracledba-ezmlmshield-x81924716.[Email Address Removed] To: "LazyDBA Discussion" <[Email address protected]
> azydba.com> cc:
> Subject: RE: can I get a range of rows using rownum
> 05/04/2005 04:15 PM
>
>
>
>
>
>
>Rownum is a value that is set only by the rows returned. Its kind of hard
>to predict what will be returned if you do anything other than a <
>condition on rownum. Your solution is to not use rownum the way you are
>using it.
>
>-----Original Message-----
>From: genegurevich
>[mailto:oracledba-ezmlmshield-x97708131.[Email address protected]
>Sent: Wednesday, May 04, 2005 4:07 PM
>To: LazyDBA Discussion
>Subject: can I get a range of rows using rownum
>
>
>
>
>
>
>hi all:
>
>I have been able to execute this:
>
>select * from TABLE1 where rownum < 100 with no problem
>
>but when I try to do where rownum > 10 and rownum < 100, I am getting odd
>results...
>
>Here is my table:
>SQL> select * from test1;
>
> F1
>----------
> 1
> 2
> 3
>
>
>4 rows selected.
>
>
>Here is the query:
>
>SQL> select * from test1 where rownum > 1 and rownum < 4
> 2 ;
>
>no rows selected
>
>Why do I get no rows here? Any thoughts?
>
>thank you
>
>Gene Gurevich
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these terms:
>http://www.lazydba.com/legal.html
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these terms:
>http://www.lazydba.com/legal.html
>
>
>
>
>
>
>
>--------
>website: http://www.LazyDBA.com
>Please don't reply to RTFM questions
>Oracle documentation is here: http://tahiti.oracle.com
>To unsubscribe: see http://www.lazydba.com/unsubscribe.html
>To subscribe: see http://www.lazydba.com
>By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
>
>
>
>

Oracle LazyDBA home page