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.
When the LIMIT clause immediately follows an ORDER BY clause, the scope of the LIMIT operation applies to the same scope as the ORDER BY (typically, the whole query). However, when used in a UNION query, the LIMIT 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 LIMIT 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 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
