Fetching rows in DB2

Question

I know in DB2 (using version 9.7) I can select the first 10 rows of a table by using this query:

SELECT * 
FROM myTable
ORDER BY id
FETCH FIRST 10 ROWS ONLY

But how can I get, for example, rows 11 to 20? I can't use the primary key or the ID to help me...

Thanks in advance!

Without the explicit order there's no guarantee that you will fetch the same 10 rows every time. You'll need to add `ORDER BY` to your query. — mustaccio, Jul 25 '13 at 16:18
You're right. I just updated my query. Thanks for pointing this out! — David Caissy, Jul 25 '13 at 18:03

score 16 · Accepted Answer · edited Jul 25 '13 at 16:34

16

Here's a sample query that will get rows from a table contain state names, abbreviations, etc.

SELECT *
FROM (
   SELECT stabr, stname, ROW_NUMBER() OVER(ORDER BY stname) AS rownumber
   FROM states
   WHERE stcnab = 'US'
) AS xxx
WHERE rownumber BETWEEN 11 AND 20 ORDER BY stname

Edit: ORDER BY is necessary to guarantee that the row numbering is consistent between executions of the query.

edited Jul 25 '13 at 16:34

mustaccio

18,234
16
48
57

answered Jul 25 '13 at 15:15

Benny Hill

6,191
4
39
59

Yes, it did the job. Thanks! – David Caissy Jul 25 '13 at 17:24
whenever I add the where clause in the end I get `SQL Error [42703]: ROWNUMBER IS NOT VALID IN THE CONTEXT WHERE IT IS USED. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.19.26` – Gabriel Ziegler Aug 31 '20 at 15:22
@GabrielZiegler - it sounds like you've got a syntax error in your query. Be sure you are enclosing your entire FROM clause in parents ( ) and have the "AS xxx" part to define your temporary table. – Benny Hill Sep 04 '20 at 20:35

score 2 · Answer 2 · answered Jul 25 '13 at 15:38

You can also use the MYSQL compatibility. You just need to activate the vector compatibility for MYS, and then use Limit and Offset in your queries.

db2set DB2_COMPATIBILITY_VECTOR=MYS
db2stop
db2start

An excellent article written by DB2 experts from IBM https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/limit_offset?lang=en
Compatibility vector in InfoCenter http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html
A blog about this http://victorsergienko.com/db2-supports-limit-and-offset/

Fetching rows in DB2

2 Answers2

Linked