• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • paul wheaton
  • Ron McLeod
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:

Query to fetch 100 records in 1000

 
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 47
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 959
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot ,it worked.
 
Mintoo kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Madhavi , I got it.
 
reply
    Bookmark Topic Watch Topic
  • New Topic