6.1.1. FIRST, SKIP

Used for

Retrieving a slice of rows from an ordered set

Available in

DSQL, PSQL

Syntax

SELECT
  [FIRST <m>] [SKIP <n>]
  FROM ...
  ...
<m>, <n>  ::=
    <integer-literal>
  | <query-parameter>
  | (<integer-expression>)

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.

Characteristics of FIRST and SKIP

• 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.

• SKIP 0 is allowed but totally pointless.

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

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

• If a SKIP lands past the end of the dataset, an empty set is returned.

• 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.

DELETE FROM MYTABLE
  WHERE ID IN (SELECT FIRST 10 ID FROM MYTABLE)

Examples of FIRST/SKIP

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

select first 10 id, name from People
  order by name asc

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

select skip 10 id, name from People
  order by name asc

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

select skip ((select count(*) - 10 from People))
  id, name from People
  order by name asc

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

select first 20 skip 80 id, name from People
  order by name asc

See also

ROWS