File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes select a range of rows by SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select a range of rows by SQL" Watch "select a range of rows by SQL" New topic
Author

select a range of rows by SQL

Sue Stephen
Greenhorn

Joined: May 04, 2004
Posts: 11
Hi,
When a query returns a big resultset of 50000 rows, is it possible to use SQL to limit the rows number like only returning 2000-3000 rows, rather than using "select top 2000". Thank you for your help.


~Sue
pradeep jaladi
Ranch Hand

Joined: Nov 21, 2004
Posts: 65
option1
In SqlServer2000 we had that option to display how many rows due we want.

option 2

While getting the data from database fetch the row one by one and add up to the number of row's u want and forward the remaining to next.


Pradeep.Jaladi
Seetesh Hindlekar
Ranch Hand

Joined: Feb 13, 2004
Posts: 244
Hello Sue,

Try the Statement's getFetchSize(int)

public int getFetchSize()

throws SQLExceptionRetrieves the number of result set rows that is the default fetch size for ResultSet objects generated from this Statement object. If this Statement object has not set a fetch size by calling the method setFetchSize, the return value is implementation-specific.

Returns:
the default fetch size for result sets generated from this Statement object
Throws:
SQLException - if a database access error occurs

java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#setFetchSize(int)

Rgds,

Seetesh
Muthu Ponmozhi
Greenhorn

Joined: Dec 17, 2004
Posts: 21
if you are using jdbc you can use the functions Statement.setFetchSize() and Statement.setMaxRows to limit the no. of rows in a resultset.


happy learning....<br>Ponmozhi</br> <br>SCJP 1.5,SCWCD 1.5(preparing)</br>
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

If you are using Oracle you can restrict your data using rownum.

like rownum < 10001...

If you have some other filtering criteria then You can also use rank and dense_rank method of oracle to filter Data


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: select a range of rows by SQL