Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Question Unanswered: Selecting only few rows from within a resultset

    Inorder to implement page navigation and sorting,I am querying the DB and in my SP I
    1.Select all the records that match a particular criteria
    2.Sort the result set

    Now i need to get rows 21-40 (say) from the sorted resultset(not the table).Can anyone tell me how to do this?

    I tried using the rownumber() method,but it seems that the rownumbers are corresponding to the row entry in the table and not the resultset.

    thanx in advance....

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Fetch First N Rows

    Hi,

    There is an option of using fetch first 'n' rows only.

    Can you push the resultset into a temporary table and then try the fetch first rows only option.

    Please also provide more details on your process being followed, and the DB2 version, fixpacks and OS that you are using.

    Cheers

    Nitin.
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Feb 2004
    Posts
    6

    Not first n,but from anywhere

    I dont want to fetch first n,but i need to fetch in between two values say 21-40 or 41-60 and the like.Can u help me? Coz it is very urgent!
    thanx in advance.....

  4. #4
    Join Date
    Feb 2004
    Posts
    6

    Db2 version 7,OS is Win2K.......

    Db2 version 7,OS is Win2K.......

  5. #5
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    TryThis

    hi Ashok,

    Will populating the result set into a temporary table and then using the rownumber() on that temporary table help?

    As you said that the rownumber() works on the table, guess that should solve your purpose temporarily, till you get a better solution.

    HTH

    Nitin.
    HTH

    Nitin

    Ask the experienced rather than the learned

  6. #6
    Join Date
    Feb 2004
    Posts
    6

    I'm trying that

    I was just trying that out.Is there no other solution? As there is an option to fetch first n,i thought there will be an option to select from in between.....

  7. #7
    Join Date
    Dec 2002
    Posts
    134

    Re: I'm trying that

    Originally posted by ashokcm
    I was just trying that out.Is there no other solution? As there is an option to fetch first n,i thought there will be an option to select from in between.....
    The only solutions:
    1. row_number() over(...)
    2. fetch first 40 and skip first 20 (acceptible for small numbers)

  8. #8
    Join Date
    Feb 2004
    Posts
    6

    how to skip first 20?

    but how do i skip first 20?

  9. #9
    Join Date
    Dec 2002
    Posts
    134

    Re: how to skip first 20?

    Originally posted by ashokcm
    but how do i skip first 20?
    In the code first do resultset.next() 20 times and than start processing

    It sounds dumb, but it may be a good idea for a small page numbers. Otherwise you need to materialize (or numerate) 1000's of records (for row_number or temp table)

  10. #10
    Join Date
    Feb 2004
    Posts
    6

    thank you

    ok....thank you......i used a temp table....and it was successful......

Posting Permissions

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