LIMIT (SQL Clause)
Synopsis
SELECT ... FROM ... [ ORDER BY ... ]
LIMIT [ ALL ] count [ OFFSET start [ ROW | ROWS ] ]
Description
A LIMIT clause is used to limit the total number of rows in the result set of the 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 LIMIT 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.
Arguments
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.
The optional ALL keyword is syntactic sugar and does not impact execution.
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.
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 LIMIT 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 LIMIT 20 OFFSET 5
