Using the SKIP Option with the FIRST Option (IDS)

3If a Projection clause with the SKIP offset option also includes FIRST or LIMIT, the result set begins with 3the row whose ordinal position is (offset + 1) in 3the set of qualifying rows, rather than with the first row. The row in position 3(offset + max) is the last 3row in the result set, unless there are fewer than (offset + max) qualifying rows. The following example 3ignores the first 50 rows from table tab1, but returns 3a result set of at most 10 rows, beginning with the fifty-first row:

SELECT SKIP 50 FIRST 10 a, b FROM tab1;

3The next example uses in a query with SKIP and FIRST to 3insert no more than five rows from table tab1 into table tab2, beginning with the eleventh row:

INSERT INTO tab2 SELECT SKIP 10 FIRST 5 * FROM tab1;

3The following collection subquery returns only the eleventh 3through fifteenth qualifying rows as a collection-derived table, orders these 3five rows by the value in column a, and stores this 3result set in a temporary table.

SELECT * FROM TABLE (MULTISET (SELECT SKIP 10 FIRST 5 a FROM tab3 
3                     ORDER BY a)) INTO TEMP 

3The following INSERT statement includes a collection subquery 3whose results define a collection-derived table. The rows are ordered by the 3value in column a, and are inserted into table tab1.

INSERT INTO tab1 (a) SELECT * FROM TABLE (MULTISET (SELECT SKIP 10 FIRST 5 a 
3     FROM tab3 ORDER BY a));

3Queries that combine the FIRST or LIMIT and SKIP options 3with the ORDER BY clause can impose a unique order on the qualifying rows, 3so successive queries that increment the offset value 3by the value of max can partition the qualifying rows 3into disjunct subsets of max rows. This can support 3web applications that require a fixed page size, without requiring cursor 3management.

You can use these features in distributed queries only if all of the participating database servers support the SKIP and FIRST options.