This is also equivalent to the following MS Sql Server statement:

select top 20 * from tbl

I've been pulling my hair out trying to figure out how to do this in Oracle!

Why not wrap this query in a sub-select and use the rownum = n to limit your resultSet to size n. Hope this helps.

Rownum isn't exactly the right solution.

i mean if i want to fetch from the register # 20 TO # 40

i cannot use SELECT field1,field2,field3 FROM mytable WHERE ROWNUM >= 20 AND ROWNUM <=40

so.. i did a 'trick' to do that..

i did:

SELECT * FROM (
SELECT rowid as ID,field1,field2,field3 FROM mytable
)
WHERE id BETWEEN 20 AND 40

i hope, this work for you ;)

Actually, ROWNUM is not exactly right either. It may work here and this time, but if you ever decide to order the resultset, it will break.

See:
http://www.oracle.c..ktom.html

This thing worked for me

SELECT * FROM
(SELECT field, ROW_NUMBER() OVER (ORDER BY field) R FROM table)
WHERE R BETWEEN 0 and 100

This thing worked for me SELECT * FROM (SELECT field, ROW_NUMBER() OVER (ORDER BY field) R FROM table) WHERE R BETWEEN 0 and 100

that one did the trick !!

http://www.oracle.c..ktom.html

select *
from
( select rownum rnum, a.*
from (your_query) a
where rownum <= :M )
where rnum >= :N;

in order to get rows n through m from 'your query.'

The SQL code posted by my friend from Venezuela is simple and fast, except the code provides an error in 10g because of a little mistake:
instead of 'rowid' it has to be 'rownum'

SQL Revised:
SELECT * FROM (
SELECT ROWNUM as ID, field1, field2, field3 FROM mytable
)
WHERE id BETWEEN 20 AND 40;
1ms;

Have a nice time everybody! ;)

Thanks

Ash

thanks,
good advices

how to paging in php with database oracle?
and the same function with mysql_num_rows in oracle?
and how to calculet the rows???

Works perfectly, thanks!

select *from (select *from tab1 where rowid<=20);
why because rowid 's are always different and perninent so we can use the rowid and find the first twenty records