Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

oracle record fetch

 
sugath nelabhotla
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic