LIMIT and OFFSET

LIMIT and OFFSET allow you to retrieve a selection of the rows that are generated by the query. Using limit and offset in combination give the ability to return any selection of the results. LIMIT provides the ability to restrict the number of results returned to a certain number. No more than the limit of rows will be returned (but possibly less, if the query itself yields less rows). OFFSET provides the ability to skip a number of rows before beginning to return rows in the query. If both OFFSET and LIMIT are defined, then the number of OFFSET rows are skipped before starting to count the number of LIMIT rows that are returned.

When using LIMIT and OFFSET (trying to return a certain range of values), it is important to use an ORDER BY clause. This will constrain the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows.

For example, the following select statement applies a where clause filter, sorts the result using ORDER BY and returns the first 20 rows, starting from Row 50 in the results:

SELECT * from <NAMED_TABLE> WHERE <SOME_CONDITION> ORDER BY <COLUMN_NAME> LIMIT 20 OFFSET 50

where:

• NAMED_TABLE is the data source for the query in the form of a named table
• SOME_CONDITION is a valid MI SQL where clause condition
• COLUMN_NAME is a valid column in the table