Retrieving a slice of rows from an ordered set

Available in

DSQL, PSQL

Syntax

Description

Limits the amount of rows returned by the SELECT statement to a specified number or range.

The FIRST and SKIP clauses do the same job as ROWS, but neither are SQL-compliant. Unlike FIRST and SKIP, the ROWS and TO clauses accept any type of integer expression as their arguments, without parentheses. Of course, parentheses may still be needed for nested evaluations inside the expression and a subquery must always be enclosed in parentheses.

Characteristics of using ROWS *m* without a clause:

• If m is greater than the total number of records in the intermediate data set, the entire set is returned

• If m < 0, the SELECT statement call fails with an error

Calling ROWS *m* TO *n* retrieves the rows from the set, starting at row m and ending after row n — the set is inclusive.

Characteristics of using ROWS *m* with a TO clause:

• If m is greater than the total number of rows in the intermediate set and n >= m, an empty set is returned

• If m is not greater than n and n is greater than the total number of rows in the intermediate set, the result set will be limited to rows starting from m, up to the end of the set

• If m < 1 and n < 1, the SELECT statement call fails with an error

• If n = m - 1, an empty set is returned

• If n < m - 1, the SELECT statement call fails with an error

Using a TO clause without a ROWS clause:

While ROWS replaces the FIRST and SKIP syntax, there is one situation where the ROWS syntax does not provide the same behaviour: specifying SKIP *n* on its own returns the entire intermediate set, without the first n rows. The ROWS …​ TO syntax needs a little help to achieve this.

With the ROWS syntax, you need a ROWS clause in association with the TO clause and deliberately make the second (n) argument greater than the size of the intermediate data set. This is achieved by creating an expression for n that uses a subquery to retrieve the count of rows in the intermediate set and adds 1 to it.

When ROWS is used in a UNION query, the directive is applied to the unioned set and must be placed after the last SELECT statement.

If a need arises to limit the subsets returned by one or more SELECT statements inside UNION, there are a couple of options:

1. Use FIRST/SKIP syntax in these SELECT statements — bearing in mind that an ordering clause (ORDER BY) cannot be applied locally to the discrete queries, but only to the combined output.

2. Convert the queries to derived tables with their own ROWS clauses.

The following examples rewrite the used in the section about FIRST and SKIP, earlier in this chapter.

Retrieve the first ten names from the output of a sorted query on the PEOPLE table:

SELECT id, name
ORDER BY name ASC
ROWS 1 TO 10;

or its equivalent

Return all records from the PEOPLE table except for the first 10 names:

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 11 TO (SELECT COUNT(*) FROM People);

And this query will return the last 10 records (pay attention to the parentheses):

This one will return rows 81-100 from the PEOPLE table:

SELECT id, name
FROM People
ORDER BY name ASC