Restricting return values with the SKIP, LIMIT, and FIRST options

You can use the SKIP, LIMIT, and FIRST options to restrict the number of return values. The SKIP option excludes the first specified number of return values. The LIMIT and FIRST options set the maximum number of return values.

There is no default value for the max or offset parameters of the FIRST, LIMIT, or SKIP options. If no integer or integer variable follows the FIRST, LIMIT, or SKIP keyword, the database server interprets that keyword as a column identifier. If no data source in the FROM clause has a column with that name, the query fails with an error.

You can use these features in cross-server distributed queries only if all of the participating database server instances support the SKIP, LIMIT, and FIRST options.

SKIP option

The SKIP offset option specifies the number of qualifying rows to exclude, for offset an integer in the SERIAL8 range, counting from the first qualifying row.

The following example retrieves the values from all rows except the first 10 rows: 
SELECT SKIP 10 a, b FROM tab1;
You can also use a host variable to specify how many rows to exclude. In an SPL routine, you can use an input parameter or a local variable to provide this value.

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

The SKIP option is not valid in the following contexts:
  • In the definition of a view
  • In nested SELECT statements
  • In subqueries, except for table expressions in the FROM clause.

FIRST option

The FIRST max option specifies that the result set includes no more than max rows (or exactly max, if max is not greater than the number of qualifying rows). Any additional rows that satisfy the selection criteria are not returned. If there are fewer than max qualifying rows, the sorted and unsorted query results always include the same rows, but typically not in the same order.

The following example retrieves at most 10 rows from table tab1: 
SELECT FIRST 10 a, b FROM tab1;

You can use a host variable or the value of an SPL input parameter in a local variable to specify max.

The FIRST keyword is also valid in queries that include table expressions in the FROM clause. The following example returns the first 8 qualifying rows, in their order of retrieval from the table expression in the FROM clause of the outer query: 
SELECT * FROM (SELECT FIRST 8 col1 
                  FROM tab1 WHERE col1 > 50 );
The next example applies the FIRST option to the result of a UNION expression: 
SELECT FIRST 10 a, b FROM tab1 UNION SELECT a, b FROM tab2;
The FIRST option is not valid in any of the following contexts:
  • In the definition of a view
  • In nested SELECT statements
  • In subqueries, except for subqueries that specify table expressions in the FROM clause
  • In a singleton SELECT (where max = 1) within an SPL routine
  • Where embedded SELECT statements are used as expressions

Instead of the FIRST or LIMIT option in the Projection clause, you can instead include the LIMIT clause after the Projection clause and the optional ORDER BY clause. For more information on the LIMIT clause, see LIMIT Clause.

LIMIT option

LIMIT is a keyword synonym for the FIRST keyword in the Projection clause. However, you cannot substitute LIMIT for FIRST in other syntactic contexts where FIRST is valid, such as in the FETCH statement.

Combining the SKIP option and the FIRST or LIMIT option

If a Projection clause with the SKIP offset option also includes a max limit that the FIRST or LIMIT option specifies, the query result set begins with the row whose ordinal position among qualifying rows is (offset + 1), rather than with the first row that satisfies the selection criteria.

If no ORDER BY clause sorts the retrieved rows, the row in position (offset + max) is the last row in the result set, unless there are fewer than (offset + max) qualifying rows. The following example ignores the first 50 qualifying rows in table tab1, but returns a result set of at most 10 rows, beginning with the fifty-first row: 
SELECT SKIP 50 FIRST 10 a, b FROM tab1;

The SELECT clause in the next example uses the SKIP and FIRST options to insert 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;