Paginating results in DB2
|
How to paginate results in db2 when using cursors is not feasable seems to be a common issue.
Suppose you have a table like this: create table mytable(
id integer,
field1 varchar(32),
field2 varchar(32)
);
if id is a sequence, you could just do:
select * from mytable where id > 5 and id < 15
to retrieve 10 rows starting from the 5th.
Easy, but useful only when you don't have a where clause nor an order by clause; in the first case(where clause) there could be missing(filtered) rows, so you won't have the desired number of rows retrieved, in the second, the order by will mess the id progression, making it unusable.
But in db2 we can use rownumber() over(). Imagine again we want to extract 10 rows starting from the 5th, sorted by ascending field2, our sql statement would be something like:
select temp.* from (
select rownumber() over(order by field2 asc) as nr,id,field1,field2 from mytable
) as temp where nr between 5 and 15
It is just a matter of changing the values in the between clause to paginate your results.
|
Tips'n Tricks 
