rownum equivalent in db2

skumbhan · #1 (**permalink**) 11-11-05, 12:33

hi

i am trying to extract rows between 10,000 and 20,000. is there a way to do it...

i know for extracting rows from 1 to 10,000 u can specify fetch first 10000 rows only.

But to get rows between 10,000 and 20,000 is there a way?

sam

wangzhonnew · #2 (**permalink**) 11-11-05, 15:21

i don't think there's any way to do that....

n_i · #3 (**permalink**) 11-11-05, 16:28

Code:

select ... from (select ..., row_number() over (order by ...) as rownum from ...) as tmp
where tmp.rownum between ....

grahammartin · #4 (**permalink**) 11-12-05, 06:07

Sample code, using syscat.tables.....

with t as (select card, row_number() over() as col2 from syscat.tables order by 1 desc) select * from t where col2 between 11 and 20 order by 2 asc

Graham Martin
http://www.ibm.com/software/data/db2/migration/

Marcus_A · #5 (**permalink**) 11-12-05, 09:17

Quote:

Originally Posted by grahammartin

Sample code, using syscat.tables.....

with t as (select card, row_number() over() as col2 from syscat.tables order by 1 desc) select * from t where col2 between 11 and 20 order by 2 asc

There really needs to be a ORDER BY in the OVER function, otherwise one will have no idea what rows 11 thru 20 are, and the order will not be be repeatable (one will not get the same rows every time), and the query will make no sense.

Internet Services

Web Development

Digital Marketing

Consumer Tech