6.1.10. ROWS

Used for

Retrieving a slice of rows from an ordered set

Available in

DSQL, PSQL

Syntax

SELECT <columns> FROM ...
  [WHERE ...]
  [ORDER BY ...]
  ROWS m [TO n]

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.

Calling ROWS *m* retrieves the first m records from the set specified.

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

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

• If m = 0, an empty 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.

Mixing ROWS and FIRST/SKIP

ROWS syntax cannot be mixed with FIRST/SKIP syntax in the same SELECT expression. Using the different syntaxes in different subqueries in the same statement is allowed.

ROWS Syntax in UNION Queries

When ROWS is used in a UNION query, the ROWS 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.

Examples of ROWS

The following examples rewrite the examples 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
FROM People
ORDER BY name ASC
ROWS 1 TO 10;

or its equivalent

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 10;

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):

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

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

SELECT id, name
FROM People
ORDER BY name ASC
ROWS 81 TO 100;