SET ROWCOUNT

You may limit the number of rows returned by all subsequent SELECT statements within the current session by using the keyword SET ROWCOUNT.

The main difference between SET ROWCOUNT and TOP or LIMIT is that TOP affects only the current statement, while SET ROWCOUNT affects all statements issued during the current database session, until the next SET ROWCOUNT or until the session is terminated.

Syntax

SET ROWCOUNT = number

Remarks

If a SELECT statement subject to a SET ROWCOUNT condition contains an ORDER BY keyword and an index cannot be used to optimize on the ORDER BY clause, PSQL generates a temporary table. The temporary table is populated with the entire query result set. The rows in the temporary table are ordered as specified by the ORDER BY value and return the ROWCOUNT number of rows in the ordered result set.

You may turn off the ROWCOUNT feature by setting ROWCOUNT to zero:

SET ROWCOUNT = 0

SET ROWCOUNT is ignored when dynamic cursors are used.

If both SET ROWCOUNT and TOP are applied to a given query, the number of rows returned is the lower of the two values.

Examples

Also see the examples for TOP or LIMIT.

SET ROWCOUNT = 10;

SELECT * FROM person;

-- returns 10 rows