Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    9

    Unanswered: rownum equivalent in db2

    hi

    i am trying to extract rows between 10,000 and 20,000. is there a way to do it...

    i know for extracting rows from 1 to 10,000 u can specify fetch first 10000 rows only.

    But to get rows between 10,000 and 20,000 is there a way?

    sam

  2. #2
    Join Date
    Nov 2005
    Location
    Toronto
    Posts
    65
    i don't think there's any way to do that....

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Code:
    select ... from (select ..., row_number() over (order by ...) as rownum from ...) as tmp
    where tmp.rownum between ....

  4. #4
    Join Date
    Apr 2005
    Posts
    41
    Sample code, using syscat.tables.....

    with t as (select card, row_number() over() as col2 from syscat.tables order by 1 desc) select * from t where col2 between 11 and 20 order by 2 asc

    Graham Martin
    http://www.ibm.com/software/data/db2/migration/

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by grahammartin
    Sample code, using syscat.tables.....

    with t as (select card, row_number() over() as col2 from syscat.tables order by 1 desc) select * from t where col2 between 11 and 20 order by 2 asc
    There really needs to be a ORDER BY in the OVER function, otherwise one will have no idea what rows 11 thru 20 are, and the order will not be be repeatable (one will not get the same rows every time), and the query will make no sense.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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