oracle record fetch

[sugath nelabhotla]

Hi, i know that when we use select * from table where rownum < 100, we get the first 100 records from the table. Is there any way that I can get the next 100 records from the table? rownum > 100 does not work. My table is not indexed and does not a primary key. Currently I am sorting the table and keeping track of 101 record id and then triggering the second batch query using the rec id.

Thanks
Madhu

[Purushoth Thambu]

You can't use rownum with > operator. What you are trying to do is pagination.

select * from
(select rownum as row_seq, t.* from table t where rownum < :upperbound )
where row_seq between :lowerbound and :upperbound

[Shailesh Chandra]

I would recommend that you read about analytical functions of oracle. RANK DENSE_RANK , and ROW_NUMBER would help you much in this regard.

Shailesh