Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Question Unanswered: Equivalent of LIMIT and OFFSET in DB2

    I want to retrieve some specific rows in my resultset say, rows between 20th to 45th rows. is there any thing in DB2 (like LIMIT and OFFSET in sql) to do so ???

    Can any one help?


    regards,

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Look at the OLAP functions, specifically ROW_NUMBER().
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You mean MySQL? I'm asking because standardized SQL doesn't have LIMIT/OFFSET.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    --Select rows between row 4 and row 7

    SELECT *
    FROM (SELECT
    ROW_NUMBER() OVER (ORDER BY empno) AS rownumber,
    empno, firstnme
    FROM employee
    ) AS temp_employee
    WHERE rownumber between 4 and 7;
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    in v9.5

    Set the DB2 registry to support an application using DUAL and ROWNUM queries.
    db2set DB2_COMPATIBILITY_VECTOR=3

    Assuming that ROWNUM pseudocolumn support is enabled for the connected database, retrieve the twentieth to the fortieth row of a result set that is stored in a temporary table.

    SELECT TEXT FROM SESSION.SEARCHRESULTS WHERE ROWNUM BETWEEN 20 AND 40 ORDER BY ID
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  6. #6
    Join Date
    Apr 2008
    Posts
    3

    my version doesn't support row_num()

    Thanks a lot every one.... But the version I am using doesn't support "row_num()".

    @stolze, Yes I meant MySQL.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It is ROW_NUMBER() OVER ()
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Apr 2008
    Posts
    3
    Thank u Stolze... but as i have already said my version of DB2 doesn't support "row_number()" and "over()"


    any other way....???

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    No, you said you don't have support for "row_num()". I assumed you had a type and tried to point you to it.

    Maybe we can go back to square 1 and you tell us which version of DB2 you are using on which platform?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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