Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query to fetch 100 records in 1000

 
Mintoo kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all ,

I have more than 10000 records in database , due to size limit , i can not fetch all records at once.Therefore i just wanted to know is there any way that i can give size limit and get records step by step.
Like , first time i would get 100 records then another 100 and so on.

Thanks in advance.
 
Rajitha Gunawardhane
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
here are the hints

* you can use LIMIT command in SQL
Ex:
SELECT * FROM `your_table` LIMIT 0, 10

* you can use
setMaxRows(int max) method or setFetchSize(int rows) in java.sql.Statement class

Best Regards,
Rajitha
 
Mintoo kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the help.

But it seem LIMIT is not wokring in SQL. as i m using PL/SQL devloper editor. if any idea . let me know

Thanks in advance.
 
Freddy Wong
Ranch Hand
Posts: 959
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use rownum in Oracle, e.g.

[ August 21, 2008: Message edited by: Freddy Wong ]
 
Mintoo kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot ,it worked.
 
Mintoo kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All ,

thanks for the suggestation.But my Query was abit diff.
like . i using the query

Select *
From myTable
Where rownum Between 1 and 20
Order By rownum

What i need to do is , i need to keep changing the limits like on first request is 1 and 20 ,then on second request i would require data b/w 21 to 30.

I have tried the same query in oracle but after changing the limit like 21 to 30 .it does not give any output.

Suggest me if anything i have left or need to alter in the query, m using oracle as DB.

Thanks in advance.
 
Madhavi Venna
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Amit Kumar Jha:
Hi All ,

I have tried the same query in oracle but after changing the limit like 21 to 30 .it does not give any output.

Suggest me if anything i have left or need to alter in the query, m using oracle as DB.

Thanks in advance.


select * from
(select rownum as rank,m.* from myTable m )
where rank between lower_limit and upper_limit

I think this query will work for you.
 
Mintoo kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Madhavi , I got it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic