• 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:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

limiting the size of a resultset - Breaking up large resultset into chunks

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is not a question, but some answers I have come up with in the past couple of days that I thought I'd share:

one option would be to use statement.setFetchSize(int) to limit the size of results returned -- call this prior to calling statement.executeQuery()
-------------------------------------------------
Otherwise, there are ways to control the resultset size via the SELECT statement as well, the exact way to do it depends on your database:
-----------------------------
Oracle:
select your results and explicitly add the rownum to the result, then select against that and use a between clause on the rownum, like this:
SELECT TALIAS.* FROM (SELECT ROWNUM AS RWN, <column-names> FROM (SELECT <column-names> FROM table_name WHERE <....> ORDER BY <...> TALIAS) WHERE TALIAS.RWN BETWEEN 1 AND 15
plug in your column names and table name, where conditions, etc. TALIAS is an statement-scope alias for the inner SELECTion result.
just plug in different values for the BETWEEN numbers and you can control the size of the result set and your overall window to it....
-----------------------------
MySQL:
much easier, use the limit keyword at the end of your query:
All rows:
SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 0, -1
first 10 rows:
SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 10
next 20 rows:
SELECT columnA, columnB FROM table_name WHERE columnA=condition LIMIT 11, 20
LIMIT begin-index, number-to-return (-1 is all remaining rows, so LIMIT 0, -1 is the entire result set, while LIMIT 101, -1 would be all remaining results after the 100th)
---------------------------
HSQLDB:
also much easier, use the limit keyword at the beginning of your query, with no comma, and 0 in the second position means all remaining results:
all rows:
SELECT LIMIT 0 0 columnA, columnB FROM table_name WHERE columnA=condition
first 10 rows:
SELECT LIMIT 0 10 * FROM table_name WHERE columnA=condition
next 20 rows:
SELECT LIMIT 11 20 * FROM table_name WHERE ColumnA=condition
---------------------------
MSSQL (I think only applies to 2000, not 7):
TOP is similar to LIMIT for MySQL
SELECT TOP 100 * FROM table_name
you could simulate paging by subsequently calling:
SELECT TOP 200 * FROM table_name
and skipping over the first 100 rows in the resultset (not pretty but it would work)

If you have a single-valued key that you sort on, you can use TOP and a WHERE clause to paginate the result set:
SELECT TOP 100 * FROM table_name WHERE key_column > ?
and parameterize the ? with 0, 100, 200, etc.

If the key is a compound key, then you can select data into a temp table with an identity column, and then pick intervals from this one. This is similar to the Oracle way to do it.
I don't know MSSQL syntax well enough to give an example of how to select into a temp table while adding an auto-increment int column. If someone does know, please post an example of how to do it!
Once you have the temp table:
SELECT * FROM temp_table WHERE new_auto_inc_key_column BETWEEN start AND end
---------------------------
hope this helps -- I just ran into the same problem myself yesterday
 
Freddy Flintpedra
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
in the Oracle example, the greater-than symbol followed by a right-parenthesis became a smiley face -- make sure you add a ')' before TALIAS
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm going to add the keywords "ResultSet Paging" to this thread since that it what many people search on.
"Rubber Chicken",
Your name does not comply to the Javaranch Naming Policy found here. Accounts with invalid names get deleted, but you can edit your profile here
thanks,
Dave
 
Freddy Flintpedra
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Someone pointed this out to me for MSSQL 7 -- see how it flips the ORDER BY from DESC in the inner query to ASC in the 'final page' outer query -- pretty ingenious:

see also:
http://www.jguru.com/forums/view.jsp?EID=1060440
 
Freddy Flintpedra
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
from the same resource as above, for Postgres:
 
Hey, sticks and stones baby. And maybe a wee mention of my stuff:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic