FETCH (SQL Clause)

Limits the number of rows returned be a query.

Synopsis

SELECT ... FROM ... [ ORDER BY ... ]
  [ OFFSET start [ ROW | ROWS ] ]
  FETCH [ FIRST | NEXT ] count [ ROW | ROWS ] [ ONLY ]

Description

A FETCH clause determines the maximum number of rows to return from the result set of the overall SELECT query. You may specify an OFFSET to skip returning a certain number of items from the beginning of the result set.

Currently, in a subquery, using a FETCH clause following an ORDER BY clause is not supported. To use ORDER BY in a subquery, use TOP instead.

InterSystems SQL supports three styles of limiting query results: TOP, LIMIT, and FETCH. These styles are completely distinct and cannot be mixed within a query. Queries that attempt to use multiple styles at once raise a SQLCODE -386 error.

The optional keywords ROW, ROWS, FIRST, NEXT, and ONLY are all syntactic sugar and do not impact execution.

Arguments

start

A positive integer indicating how many rows to skip at the start of the result set.

The OFFSET clause is optional. Specifying zero for start produces the same behavior as omitting the OFFSET clause all together.

The ROW or ROWS options are syntactic sugar and do not impact execution.

count

A positive integer controlling how many rows the query can return. Negative integers are treated as zero.

If count exceeds the number of rows selected before the limit takes place, then the entire result set is returned.

Examples

The following example selects the first 20 Home_State values retrieved from Sample.Person in ascending collation sequence order:

SELECT Home_State FROM Sample.Person ORDER BY Home_State FETCH 20

The following example omits 5 rows and selects the next 20 Home_State values retrieved from the Sample.Person table in ascending collation sequence order:

SELECT Home_State FROM Sample.Person ORDER BY Home_State OFFSET 5 FETCH FIRST 20