Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Exclamation Unanswered: Pagination or records per page

    :: Environment DB2 V7.1.1 on Z/OS. ::

    I am looking for a sql query that would return rows 20 through 30 of a query.

    For example
    select * from ( select a.*, rownum rnum from (select job, empno from emp order job asc) a )where rnum>=20 and rnum<30

    This is in oracle, but is there a way to do this using db2 v7.1.1 on zos?

    Please let me know, I have been looking for this for a while now and have come to believe that there is no solution to this.

    Thanks.

    TP.

  2. #2
    Join Date
    Sep 2004
    Posts
    6
    I'm not sure if the EXCEPT operator is available in DB2 V7 on z/OS, but if it is, you can try the following query --

    select * from (select * from emp fetch first 5 rows only) as temp1
    EXCEPT
    select * from (select * from emp fetch first 2 rows only) as temp2
    ;

    This query would give rows 3 thru 5. An assumption made is that the table has a primary key or the column-list given by SELECT * forms a unique key across table. This would ensure desired results given the functioning of EXCEPT.

    I have tested this successfuly on DB2 V8.1 on Unix.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Santosh Kumar
    This would ensure desired results given the functioning of EXCEPT.
    I think this will only ensure that you'll get any 3 rows out of any 5. This kind of query will also ensure horrible performance given a table of a decent size.

  4. #4
    Join Date
    Sep 2004
    Posts
    6
    You are right, you got to do 'order by' to get the right rows --

    select * from (select * from emp order by job fetch first 5 rows only) as temp1
    EXCEPT
    select * from (select * from emp order by job fetch first 2 rows only) as temp2
    ;

    Performance would definitely be an issue for a huge table.

Posting Permissions

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