Query to fetch 100 records in 1000

[Mintoo kumar]

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]

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]

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]

Use rownum in Oracle, e.g.

    
select * from tbl_name where rownum <= 100

[ August 21, 2008: Message edited by: Freddy Wong ]

[Mintoo kumar]

Thanks a lot ,it worked.

[Mintoo kumar]

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]

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]

Thanks Madhavi , I got it.