Results 1 to 6 of 6

Thread: Data in Batches

  1. #1
    Join Date
    Jan 2009
    Posts
    153

    Unanswered: Data in Batches

    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

  2. #2
    Join Date
    Nov 2009
    Location
    Bangalore
    Posts
    25

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2009
    Posts
    153
    Thanks All,

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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    ;

Posting Permissions

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