"and rownum between..." in SQL Statement

[MikeJones]

Hi,

I'm trying to write a query that will reurn a subset of the total results, so at the end of the statement I have a...

and rownum between x and y

If I have x as any value other than 1 though it stops the query bringing back any results. For example

select * from dba_tables where rownum between 2 and 10

Returns nothing

But

select * from dba_tables where rownum between 1 and 10

returns stuff. Any ideas??

 

[carp]

Actually, I'm surprised that "between 1 and 10" works!
Since you can't specify something like &quot;rownum > 5&quot; and &quot;between 1 and 10&quot; translates to &quot;>=1 AND <= 10&quot;, it shouldn't work.

The only thing I've found that works for this situation is also just a bit ugly:

SELECT * FROM dba_tables WHERE rownum < 11
MINUS
SELECT * FROM dba_tables WHERE rownum < 2;

This still has an inherent problem in that there's no guarantee that the rows will be returned in the same order by both queries.

 

[MikeJones]

Thanks Carp.

I had to modify an existsing query which was fairly complex and so turned the whole query into an ILV and then also selected out the rownum with an alias. In the where condition of the query I could then refer to the rownum of the original query as a static column and do a between on it. i.e.

select table_name
from dba_tables
where rownum between 2 and 10

becomes...
select table_name
from (select table_name, rownum, row_num
from dba_tables) ilv
where ilv.row_num between 2 and 10

It also seems to have kept the query plan very stable, for the ilv part anyway which is a god send as I'd not fancy trying to re-hint.