CODEWITHSUNDEEP
db2fetch
David Caissy
David Caissy

Reputation: 2239

Fetching rows in DB2

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!

Upvotes: 15

Views: 87065

Answers (2)

Benny Hill
Benny Hill

Reputation: 6240

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.

Upvotes: 16

AngocA
AngocA

Reputation: 7693

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

Upvotes: 2

Related Questions