FETCH (SQL Clause)
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 clause to skip returning a certain number of items from the beginning of the result set.
When the FETCH (or OFFSET) clause immediately follows an ORDER BY clause, the scope of the FETCH operation applies to the same scope as the ORDER BY (typically, the whole query). However, when used in a UNION query, the FETCH operation may be applied to a single leg of the query. The application depends on the syntactic structure of your statement, as described in the table below.
| Query | Segment FETCH Applies To |
|---|---|
| SELECT * FROM t1 UNION SELECT * FROM t2 LIMIT 1 | second leg |
| (SELECT * FROM t1 UNION SELECT * FROM t2) LIMIT 1 | whole query |
| (SELECT * FROM t1) UNION (SELECT * FROM t2) LIMIT 1 | whole query |
| SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY f1 LIMIT 1 | whole query |
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 keywords ROW, ROWS, FIRST, NEXT, and ONLY are optional: they are available to improve readability, and do not affect 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 keywords are optional: they are available to improve readability, and do not affect 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
