Data in Batches

ratheeshknair · #1 (**permalink**) 11-12-09, 23:29

Hi Experts,

How can i select data in batches?

Can we create a select query to fetch the rows from 10 to 20 as like the functionality of row_num() in oracle??

We should fetch the rows which we specify for the pagination.

Please Help

Thanks in Advance

sawangupta · #2 (**permalink**) 11-13-09, 00:18

Sure

Use OptimizeForNRows keyword setting for your application.
OptimizeForNRows CLI/ODBC configuration keyword - IBM DB2 9.7 for Linux, UNIX, and Windows

Specify the Rowset Size
Specifying the rowset returned from the result set - IBM DB2 9.7 for Linux, UNIX, and Windows

Regards,
Sawan Gupta

Marcus_A · #3 (**permalink**) 11-13-09, 00:55

WITH T (EMP_NO, LAST_NAME, FIRST_NAME, rnum) AS
(SELECT EMPNO, LASTNAME, FIRSTNME, row_number() over(order by LASTNAME) FROM EMPLOYEE WHERE EDLEVEL > 12)
SELECT * FROM T where rnum BETWEEN 5 AND 10

ratheeshknair · #4 (**permalink**) 11-15-09, 00:59

Thanks All,

Can i get the same in a single query? ie without the nested query.

tonkuma · #5 (**permalink**) 11-15-09, 01:31

I don't know other ways without using nested query.

But, the common table expression can be replaced by a nested table expression, in this case.

WITH T(...) AS (SELECT ... )
SELECT * FROM T where rnum BETWEEN 5 AND 10
is equivalent to
SELECT * FROM (SELECT ... ) AS T(...) where rnum BETWEEN 5 AND 10

tonkuma · #6 (**permalink**) 11-15-09, 19:47

Quote:

I don't know other ways without using nested query.

Except the following self-join which is usually inefficient and not practical.

Code:

SELECT a.empno, a.lastname, a.firstnme
     , COUNT(b.lastname) AS rnum
  FROM employee a
     , employee b
 WHERE a.edlevel > 12
   AND b.edlevel > 12
   AND b.lastname <= a.lastname
 GROUP BY
       a.empno, a.lastname, a.firstnme
HAVING COUNT(b.lastname) BETWEEN 5 AND 10
 ORDER BY
       COUNT(b.lastname)
;

Internet Services

Web Development

Digital Marketing

Consumer Tech