:: Environment DB2 V7.1.1 on Z/OS. ::

I am looking for a sql query that would return rows 20 through 30 of a query.

For example
select * from ( select a.*, rownum rnum from (select job, empno from emp order job asc) a )where rnum>=20 and rnum<30

This is in oracle, but is there a way to do this using db2 v7.1.1 on zos?

Please let me know, I have been looking for this for a while now and have come to believe that there is no solution to this.

Thanks.

TP.

I'm not sure if the EXCEPT operator is available in DB2 V7 on z/OS, but if it is, you can try the following query --

select * from (select * from emp fetch first 5 rows only) as temp1
EXCEPT
select * from (select * from emp fetch first 2 rows only) as temp2
;

This query would give rows 3 thru 5. An assumption made is that the table has a primary key or the column-list given by SELECT * forms a unique key across table. This would ensure desired results given the functioning of EXCEPT.

I have tested this successfuly on DB2 V8.1 on Unix.

I think this will only ensure that you'll get any 3 rows out of any 5. This kind of query will also ensure horrible performance given a table of a decent size.

You are right, you got to do 'order by' to get the right rows --

select * from (select * from emp order by job fetch first 5 rows only) as temp1
EXCEPT
select * from (select * from emp order by job fetch first 2 rows only) as temp2
;

Performance would definitely be an issue for a huge table.