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....

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.

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.....

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

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.

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)

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)

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