Using the SKIP Option (IDS)

The SKIP offset option specifies how many of the 3qualifying rows to exclude, for offset an integer 3in the SERIAL8 range, counting from the first qualifying row. The following 3example retrieves the values fom all rows except the first 10 rows:

SELECT SKIP 10 a, b FROM tab1;

You can also use a host variable to specify 3how many rows to exclude. In an SPL routine, you can use an input parameter 3or a local variable to provide this value.

When you use the SKIP option in a query with an ORDER BY clause, you can 3exclude the first offset rows that have the lowest 3values according to the ORDER BY criteria. You can also use SKIP to exclude 3rows with the highest values, if the ORDER BY clause includes the DESC keyword. 3For example, the following query returns all rows of the orders table, except for the fifty oldest orders:

SELECT SKIP 50 * FROM orders ORDER BY order_date

Here the result set is empty if there are fewer than 50 rows in the orders table. An offset = 0 is not 3invalid, but in that case the SKIP option does nothing.

You can also use the 3SKIP3 option to restrict the 3result sets of prepared SELECT statements, of UNION queries, in queries whose 3result set defines a collection-derived table, and in the events and actions 3of triggers.

You can use the SKIP and the FIRST options together to specify which and 3how many qualifying rows are in the result set, as illustrated by examples 3in the section Using the SKIP Option with the FIRST Option (IDS).

The SKIP option is not valid in the following contexts:

3
• In the definition of a view
3
• In nested SELECT statements
3
• In subqueries.