Hi,
I hope this query will suffice your need,
select * from (select * from test1 where rowid not in(select rowid from test1
where rownum <&&from)) a where rownum <=&to-&from;
All the best
GnanaSekar. A
==========================================================================
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] "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
>
>
>
>
>
--------
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
------------------------------------------------------------------------------
The contents of this e-mail are confidential to the ordinary user of the
e-mail address to which it was addressed and may also be privileged. If you
are not the addressee of this e-mail you should not copy, forward, disclose or
otherwise use it or any part of it in any form whatsoever. If you have
received this e-mail in error please notify us by telephone or e-mail the
sender by replying to this message, and then delete the e-mail and other
copies of it from your computer system. Thank you.
We believe this email to be virus free but do not warrant that this is the
case and we will not accept liability for any losses arising from any virus
being transmitted unintentionally by us.
We reserve the right to monitor all E-mail communications through our network
Oracle LazyDBA home page