Retrieving a slice of rows from an ordered set

Available in

DSQL, PSQL

Syntax

Description

FIRST limits the output of a query to the first m rows. SKIP will suppress the given n rows before starting to return output.

FIRST and SKIP are both optional. When used together as in “FIRST *m* SKIP *n*”, the n topmost rows of the output set are discarded and the first m rows of the rest of the set are returned.

  • Any argument to FIRST and SKIP that is not an integer literal or an SQL parameter must be enclosed in parentheses. This implies that a subquery expression must be enclosed in two pairs of parentheses.

  • FIRST 0 is also allowed and returns an empty set.

  • Negative SKIP and/or FIRST values result in an error.

  • If the number of rows in the dataset (or the remainder left after a SKIP) is less than the value of the m argument supplied for FIRST, that smaller number of rows is returned. These are valid results, not error conditions.

Examples of FIRST/SKIP

The following query will return the first 10 names from the People table:

The following query will return everything but the first 10 names:

  1. select skip 10 id, name from People
  2.   order by name asc

And this one returns the last 10 rows. Notice the double parentheses:

This query returns rows 81 to 100 of the People table:

  1. select first 20 skip 80 id, name from People

See also