Row limitation clauses in SELECT, UPDATE, and DELETE query blocks

The FIRST, TOP, and LIMIT clauses allow you to return, update, or delete a subset of the rows that satisfy the WHERE clause. The FIRST, TOP, and LIMIT clauses can be used within any SELECT query block that includes an ORDER BY clause. The FIRST and TOP clauses can also be used in DELETE and UPDATE query blocks.

The FIRST, TOP, and LIMIT clauses are row limitation clauses and they have the following syntax:

row-limitation-option-1 :
FIRST | TOP { ALL | limit-expression } [ START AT startat-expression ]

row-limitation-option-2 :
LIMIT { [ offset-expression, ] limit-expression | limit-expression OFFSET offset-expression }

limit-expression : simple-expression

startat-expression : simple-expression

offset-expression : simple-expression

simple-expression :
integer
| variable
| ( simple-expression )
| ( simple-expression { + | - | * } simple-expression )

Only one row limitation clause can be specified for a SELECT clause. When specifying these clauses, an ORDER BY clause is required to order the rows in a meaningful manner.

row-limitation-option-1 This type of clause can be used with SELECT, UPDATE, or DELETE query blocks. The TOP and START AT arguments can be simple arithmetic expressions over host variables, integer constants, or integer variables. The TOP argument must evaluate to a value greater than or equal to 0. The START AT argument must evaluate to a value greater than 0. If startat-expression is not specified the default is 1.
The expression limit-expression + startat-expression -1' must evaluate to a value less than 9223372036854775807 = 2^64-1. If the argument of TOP is ALL, all rows starting at startat-expression are returned.

The TOP limit-expression START AT startat-expression clause is equivalent to LIMIT (startat-expression-1), limit-expression or LIMIT limit-expression OFFSET (startat-expression-1).
row-limitation-option-2 This type of clause can be used only in SELECT query blocks. The LIMIT and OFFSET arguments can be simple arithmetic expressions over host variables, integer constants, or integer variables. The LIMIT argument must evaluate to a value greater than or equal to 0. The OFFSET argument must evaluate to a value greater than or equal to 0. If offset-expression is not specified, the default is 0. The expression limit-expression + offset-expression must evaluate to a value less than 9223372036854775807 = 2^64-1.

The row limitation clause LIMIT offset-expression, limit-expression is equivalent to LIMIT limit-expression OFFSET offset-expression. Both of these constructs are equivalent to TOP limit-expression START AT (offset-expression + 1).

The LIMIT keyword is disabled by default. Use the reserved_keywords option to enable the LIMIT keyword.

Examples

The following query returns information about the employee that appears first when employees are sorted by last name:

SELECT FIRST *
FROM Employees
ORDER BY Surname;

The following queries return the first five employees when their names are sorted by last name:

SELECT TOP 5 *
FROM Employees
ORDER BY Surname;

SELECT *
FROM Employees
ORDER BY Surname
LIMIT 5;

When you use TOP, you can also use START AT to provide an offset. The following statements list the fifth and sixth employees sorted in descending order by last name:

SELECT TOP 2 START AT 5 *
FROM Employees
ORDER BY Surname DESC;

SELECT *
FROM Employees
ORDER BY Surname DESC
LIMIT 2 OFFSET 4;

SELECT *
FROM Employees
ORDER BY Surname DESC
LIMIT 4,2;

FIRST and TOP should be used only with an ORDER BY clause to ensure consistent results. Using FIRST or TOP without an ORDER BY causes a syntax warning, and can yield unpredictable results.

CREATE OR REPLACE VARIABLE atop INT = 10;

The following queries return the first five employees when their names are sorted by last name:

SELECT TOP (atop -5) *
FROM Employees
ORDER BY Surname;

SELECT *
FROM Employees
ORDER BY Surname
LIMIT (atop-5);

The following statements list the fifth and sixth employees sorted in descending order by last name:

SELECT TOP (atop - 8) START AT (atop -2 -3) *
FROM Employees
ORDER BY Surname DESC;

SELECT *
FROM Employees
ORDER BY Surname DESC
LIMIT (atop - 8) OFFSET (atop -2 -3 -1);

Row limitation clauses in SELECT, UPDATE, and DELETE query blocks

Examples

See also