Results 1 to 10 of 10

Thread: limit in DB2

  1. #1
    Join Date
    Jan 2011
    Posts
    4

    Unanswered: limit in DB2

    i have a simmple query as

    select * from my_table

    i want put some row limit, for example from 20 to 30

    in mysql is select * from my_table limit x,y

    how it is in db2?

    it's important that i want all columns, so i use *

    thanks and sorry for my bad english

  2. #2
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    How about using fetch first 20 rows only in SQL select statement.

  3. #3
    Join Date
    Jan 2011
    Posts
    4
    Quote Originally Posted by schintala View Post
    How about using fetch first 20 rows only in SQL select statement.
    it could also take up the rows from 41 to 60 (example) or juste the firts 20 (from 1 t 20)?

    thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I haven't tested this code, but should look something like this:
    Code:
    SELECT *
    FROM ( SELECT
           ROWNUMBER() OVER(ORDER BY COL1) AS ROWNUMB,
           COL1,
           COL2,
           COL3,
           COL4
           FROM your_table) AS T
    WHERE ROWNUMB BETWEEN 20 AND 30;
    Please note that you should have an ORDER BY in the OVER clause for this to be meaningful, since without that you cannot guarantee the same results each time you run it. This is a fundamental principle of all relational databases.
    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
    Jan 2011
    Posts
    4
    Quote Originally Posted by Marcus_A View Post
    I haven't tested this code, but should look something like this:
    Code:
    SELECT *
    FROM ( SELECT
           ROWNUMBER() OVER(ORDER BY COL1) AS ROWNUMB,
           COL1,
           COL2,
           COL3,
           COL4
           FROM your_table) AS T
    WHERE ROWNUMB BETWEEN 20 AND 30;
    Please note that you should have an ORDER BY in the OVER clause for this to be meaningful, since without that you cannot guarantee the same results each time you run it. This is a fundamental principle of all relational databases.
    i try tomorrow, now i'm at home

    thanks

  6. #6
    Join Date
    Jan 2011
    Posts
    4
    ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO SQL Code: -104, SQL State: 42601

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    clockdva, Marcus_A did mention he was doing this without testing. There is a small typo in the query. It should be ROW_NUMBER() instead of ROWNUMBER()
    Code:
    WITH your_table (COL1, COL2, COL3, COL4)
      AS (SELECT 1, 'A', 'AA', 'AAA' FROM SYSIBM.SYSDUMMY1
          UNION ALL
          SELECT COL1 + 1, COL2, COL3, COL4 FROM your_table
          WHERE COL1 < 50
         )
    SELECT *
    FROM ( SELECT
           ROW_NUMBER() OVER(ORDER BY COL1) AS ROWNUMB,
           COL1,
           COL2,
           COL3,
           COL4
           FROM your_table) AS T
    WHERE ROWNUMB BETWEEN 20 AND 30;
    ;
    
    ROWNUMB              COL1        COL2 COL3 COL4
    -------------------- ----------- ---- ---- ----
                      20          20 A    AA   AAA 
                      21          21 A    AA   AAA 
                      22          22 A    AA   AAA 
                      23          23 A    AA   AAA 
                      24          24 A    AA   AAA 
                      25          25 A    AA   AAA 
                      26          26 A    AA   AAA 
                      27          27 A    AA   AAA 
                      28          28 A    AA   AAA 
                      29          29 A    AA   AAA 
                      30          30 A    AA   AAA 
    
      11 record(s) selected.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... It should be ROW_NUMBER() instead of ROWNUMBER()
    ROWNUMBER() may be valid.

    IBM i DB2 for i SQL Reference 7.1
    Note: Syntax alternatives: DENSERANK can be specified in place of
    DENSE_RANK, and ROWNUMBER can be specified in place of
    ROW_NUMBER.
    DB2 Version 9.1 for z/OS SQL Reference
    Syntax alternatives and synonyms: For compatibility, the keywords DENSERANK
    and ROWNUMBER can be used as synonyms for DENSE_RANK and
    ROW_NUMBER respectively.
    IBM DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 1
    The ROW_NUMBER (or ROWNUMBER) function computes the sequential row
    number of the row within the window defined by the ordering, starting with 1
    for the first row.

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Thanks for the correction, tonkuma. I went straight to the examples and missed that part of the manual.

    clockdva, if you could post your SQL with the error, someone may be able to find the problem (if you can't).

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought clockdva might be using older version of DB2 on which OLAP specifications were not supported.

Posting Permissions

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