limit in DB2

clockdva · #1 (**permalink**) 01-17-11, 13:56

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

schintala · #2 (**permalink**) 01-17-11, 14:55

How about using fetch first 20 rows only in SQL select statement.

clockdva · #3 (**permalink**) 01-17-11, 15:43

Quote:

Originally Posted by schintala

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

Marcus_A · #4 (**permalink**) 01-17-11, 16:01

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.

clockdva · #5 (**permalink**) 01-17-11, 16:04

Quote:

Originally Posted by Marcus_A

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

clockdva · #6 (**permalink**) 01-18-11, 04:27

ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO SQL Code: -104, SQL State: 42601

Stealth_DBA · #7 (**permalink**) 01-18-11, 06:17

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.

tonkuma · #8 (**permalink**) 01-18-11, 07:16

Quote:

... It should be ROW_NUMBER() instead of ROWNUMBER()

ROWNUMBER() may be valid.

IBM i DB2 for i SQL Reference 7.1

Quote:

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

Quote:

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

Quote:

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.

Stealth_DBA · #9 (**permalink**) 01-18-11, 07:22

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).

tonkuma · #10 (**permalink**) 01-18-11, 07:58

I thought clockdva might be using older version of DB2 on which OLAP specifications were not supported.

Internet Services

Web Development

Digital Marketing

Consumer Tech