Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: how to limit a result set start point

    I'm just converting to DB2 8.1 right now and I'm having a hard time finding any information at all on how (or if it's possible at all) to limit a result set to a start_index and items_to_return.

    I've found "fetch first N rows only" which is would solve items_to_return, but so far the only way I've found to do start_index is to just keep calling next() on the result set. (Yes I'm using java/JDBC)

    As an example in mysql there's a thing called "limit x,y" where x is the starting row # and y is the number of rows to return. Is there such a thing in DB2?

    Thanx

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Here is an example where you can define row numbers to the result set [with row_number() over…] and then skip over a certain number of rows.

    SELECT *
    FROM ( SELECT ...,
    row_number() over(order by s.crtd_dtt desc, s.tableA_id)
    FROM tableA_t s
    WHERE s.tableA_id > '65185' AND
    s.display_status = 5005 ) AS inner(..., rnum)
    WHERE rnum BETWEEN 37 AND 99

    The above query selects rows 37 through 99 in the result set. You would need to add the “select first n rows only”.

    The above example was posted by Knut Stolze on the comp.databases.ibm-db2 newsgroup. Here is a link to post on the google newsgroup archive:
    http://groups.google.com/groups?dq=&...b2%26start%3D0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •