LIMIT Clause

The LIMIT clause can restrict the result set of the query to some maximum number of rows. If this clause specifies a value smaller than the number of qualifying rows, the query returns only a subset of the rows that satisfy the selection criteria.

The LIMIT clause is an extension to the ISO/ANSI standard for the SQL language. This syntax fragment is part of the SELECT statement.

LIMIT clause

Notes:

¹ HCL OneDB™ extension


Element	Description	Restrictions	Syntax
max	Integer (> 0) specifying maximum number of rows to return	If max > number of qualifying rows then all matching rows are returned	Literal Number
max_var	Host variable or local SPL variable storing the value of max	Same as max; valid in prepared objects and in SPL routines	Language dependent

Usage

The LIMIT clause specifies that the result set includes no more than max rows (or exactly max rows, if max is less than the number of qualifying rows). Any additional rows that satisfy the query selection criteria are not returned.

The following example sets max with a literal integer to retrieve at most 10 rows from table tab1:

SELECT a, b FROM tab1 LIMIT 10;

You can also use a host variable, or the value of an SPL input parameter in a local variable, to assign the value of max.

If the LIMIT clause follows the ORDER BY clause, the returned rows are sorted according to the ORDER BY specifications. Because query results are generally not retrieved in any particular sequence, using ORDER BY to constrain the order of rows can be useful in queries whose LIMIT clause returns only a subset of the qualifying rows.

For example, the following query returns data about the ten highest-paid employees:

SELECT name, salary FROM emp 
   ORDER BY salary DESC LIMIT 10;

You can use the LIMIT clause in a query whose result set defines a collection-derived table (CDT) within the FROM clause of another SELECT statement. The following query specifies a CDT that has no more than ten rows:

SELECT * 
   FROM TABLE(MULTISET(SELECT * FROM employees 
   ORDER BY employee_id LIMIT 10 )) vt(x,y), tab2 
   WHERE tab2.id = vt.x;

The next example applies the LIMIT clause to the result of a UNION query:

SELECT a, b FROM tab1 LIMIT 10 UNION SELECT a, b FROM tab2;

Restrictions on the LIMIT clause

The LIMIT clause is not valid in any of the following contexts:

In the definition of a view
In nested SELECT statements
In subqueries, except for subqueries that specify table expressions in the FROM clause
In a singleton SELECT (where max = 1) within an SPL routine
Where embedded SELECT statements are used as expressions.

Dependencies between the LIMIT clause and Projection clause

Syntax of the LIMIT clause that can follow the ORDER BY clause resembles the syntax of the FIRST option in the Projection clause, where LIMIT is valid as a keyword synonym for FIRST. The following examples are both valid, and both queries return the same results:

SELECT FIRST 5 c1, c2 FROM tab ORDER BY c3;
SELECT c1, c2 FROM tab ORDER BY c3 LIMIT 5;

Whether or not a query that uses the LIMIT clause also specifies ORDER BY can affect which qualifying rows are in the result sent if the Projection clause includes the SKIP options. The SKIP option, which specifies an offset for the max qualifying rows that FIRST or LIMIT restricts, is valid only in the Projection clause, but its specification can affect which of the sorted rows the LIMIT clause includes in the query result.

The following query retrieves two column values from each row of table tab, but the SKIP option excludes the first twenty rows. After ORDER BY sorts the rows by their value in a third column, the LIMIT clause restricts the query result to only the ten rows with the smallest values in column c3:

SELECT SKIP 20 c1, c2 FROM tab ORDER BY c3 LIMIT 10;

Combining the SKIP option and the LIMIT clause is also valid in queries that include table expressions in the FROM clause, as in this example:

SELECT * FROM (SELECT SKIP 2 col1 FROM tab1 
                  WHERE col1 > 50 LIMIT 8);

But because you cannot use FIRST as a synonym for LIMIT outside the Projection clause, the following query fails with a syntax error:

SELECT SKIP 20 c1, c2 FROM tab ORDER BY c3 FIRST 10;

The database server also issues an error if the FIRST or LIMIT max value in the Projection clause differs from the max value in the LIMIT clause, if both max specifications are in the same query block and apply to the same query result set, as in these bad examples:

SELECT FIRST 20 c1, c2 FROM tab ORDER BY c3 LIMIT 10;
SELECT LIMIT 10 c1, c2 from tab ORDER BY c3 LIMIT 20;

No error is issued, however, if both the FIRST or LIMIT option in the Projection clause and the LIMIT clause specify the same max value. The following examples are equivalent and valid:

SELECT LIMIT 20 c1, c2 FROM tab ORDER BY c4;
SELECT c1, c2 FROM tab ORDER BY c4 LIMIT 20;

Besides the SKIP, FIRST, and LIMIT options, the DISTINCT and UNIQUE specifications of the Projection clause can also restrict query results to a subset of the qualifying rows, as their Projection clause topics explain.