Hitachi

Hitachi Advanced Database SQL Reference

7.9.1 Specification format and rules for LIMIT clauses

The LIMIT clause specifies the maximum number of rows that will be retrieved from the results of a query expression or query expression body.

A LIMIT clause can be specified in the following locations:

#

Only a table derived by a table subquery applies. The derived tables in 7.9.1 Specification format and rules for LIMIT clauses refer to tables derived by table subqueries.

Organization of this subsection

(1) Specification format

■ Specifying a LIMIT clause in the outermost query expression of a SELECT statement or in the outermost query expression body of a COPY statement
LIMIT-clause ::= LIMIT [offset,]row-count
 
  offset ::= value-specification
  row-count ::= value-specification
■ Specifying a LIMIT clause in a derived table, scalar subquery, WITH clause, or CREATE VIEW statement
LIMIT-clause ::= LIMIT row-count
 
  row-count ::= value-specification

To Page Top

(2) Explanation of specification format

offset-row-count:

Specifies the number of rows to skip from the beginning of the search results of a query expression or query expression body. For example, if "LIMIT 10,5" is specified (offset-row-count is 10, limit-row-count is 5), the first 10 rows of the search results of a query expression or query expression body are skipped and rows 11 through 15 are obtained.

The following specification rules apply:

  • The offset row count cannot be specified in a derived table, scalar subquery, WITH clause, or in a LIMIT clause specified in a CREATE VIEW statement.

  • Specify the offset row count in the form of a value specification. For details about value specifications, see 7.22 Value specification.

  • Specify an integer between 0 and 2,147,483,647 (BIGINT type# data) for offset-row-count.

  • If 0 is specified for offset-row-count, the number of rows specified for the limit-row-count is obtained from the first row of the search results of a query expression or query expression body, as if the offset row count was not specified.

  • If you specify a dynamic parameter for offset-row-count, the assumed data type of the dynamic parameter is BIGINT type#.

  • A null value cannot be specified for offset-row-count.

#

If the integer data type format is a legacy format, it will be an INTEGER type.

limit-row-count:

Specifies the maximum number of rows to be obtained from the search results of the query expression or query expression body.

The following specification rules apply:

  • Specify limit-row-count in the form of a value specification. For details about value specifications, see 7.22 Value specification.

  • Specify an integer between 0 and 2,147,483,647 (BIGINT type# data) for limit-row-count.

  • If 0 is specified for limit-row-count, the search result will be 0 rows.

  • If you specify a dynamic parameter for limit-row-count, the assumed data type of the dynamic parameter is BIGINT type#.

  • A null value cannot be specified for limit-row-count.

#

If the integer data type format is a legacy format, it will be an INTEGER type.

To Page Top

(3) Rules

(a) Rules for specifying a LIMIT clause in the outermost query expression of a SELECT statement or in the outermost query expression body of a COPY statement

  1. When a LIMIT clause is specified, the number of rows in the results of the query expression or query expression body will be the following:

    MAX{MIN(number of rows in results of query expression or query expression body when no LIMIT clause is specified - offset, row-count), 0 }

  2. If the number of rows in the results of the query expression or query expression body is greater than the sum of offset and row-count, the retrieval results will not be uniquely determined in the following cases:

    • When no ORDER BY clause is specified

    • When an ORDER BY clause is specified, but there is another row with the same sort key value as the last row of the results retrieved by the LIMIT clause (see Example 1)

    • When an ORDER BY clause is specified, but there is another row with the same sort key value as the last row that was skipped by specifying the offset (see Example 2)

Example 1

This example searches the sales history table (SALESLIST) by executing the following SELECT statement, setting row-count to 2.

SELECT "USERID","PUR-CODE","PUR-NUM"
    FROM "SALESLIST"
       ORDER BY "PUR-NUM" ASC
       LIMIT 2

[Figure]

Explanation

The ORDER BY clause arranges the results of the query expression in ascending order, using the value of the PUR-NUM column as the sort key.

Because of row-count, the first two rows are retrieved as the retrieval results.

Because there are two rows with the same sort key (3) as the last row (row 2), the retrieval results are indeterminate.

Example 2

This example searches the sales history table (SALESLIST) by executing the following SELECT statement, setting offset to 2, and row-count to 1.

SELECT "USERID","PUR-CODE","PUR-NUM"
    FROM "SALESLIST"
       ORDER BY "PUR-NUM" ASC
       LIMIT 2,1

[Figure]

Explanation

The ORDER BY clause arranges the results of the query expression in ascending order, using the value of the PUR-NUM column as the sort key.

Because of offset, the first two rows are skipped.

Because there are two rows with the same sort key (3) as the last skipped row (row 2), the retrieval results vary depending on which row is skipped.

(b) Rules when specifying a LIMIT clause in a derived table, scalar subquery, WITH clause, or CREATE VIEW statement

  1. Note that the LIMIT clause cannot be specified for the following derived table.

    • A derived table that references a table that is outside the derived table in which the LIMIT clause is specified

      Example of an SQL statement that generates an error:

      [Figure]

      In this example, "T1"."C1" references a table that is outside the derived table in which the LIMIT clause is specified (correlation name: DRV). The LIMIT clause is therefore not permitted here.

      For details about derived tables, see 7.11.1 Specification format for table references.

  2. When the LIMIT clause is specified, the result row count in the query expression body is as follows.

    MIN (Result row count and limit row count in the query expression body when the LIMIT clause is not specified)

  3. If the result row count of the query expression body is outside the limit row count, the retrieval result will not be uniquely determined in the following cases.

    • When a LIMIT clause is specified for a derived table, scalar subquery, or WITH clause (because an ORDER BY clause cannot be specified for these specifications)

    • When retrieving a viewed table defined by a CREATE VIEW statement with a LIMIT clause (because the ORDER BY clause cannot be specified in the CREATE VIEW statement)

    Example:

    CREATE VIEW "SALESLIST_VIEW" AS SELECT * FROM "SALESLIST" LIMIT 2
SELECT * FROM "SALESLIST_VIEW" ORDER BY "USERID"

    When the SELECT statement above is executed, the retrieval result is not uniquely determined as follows.

    [Figure]

    The above describes only two examples of retrieval results, but other retrieval results may be displayed as well.

  4. If a LIMIT clause is specified for a query expression body that references an outer query column, the entire row count of the result of that query expression body is not subject to the LIMIT clause. In this case, the number of rows of the result of the query expression body for a single value in the outer query column is the target of the LIMIT clause.

    Example:

    SELECT (SELECT "PRODUCTLIST"."PUR-NAME" FROM "PRODUCTLIST"
          WHERE "SALESLIST"."PUR-CODE"="PRODUCTLIST"."PUR-CODE" LIMIT 1)
       ,"SALESLIST"."PUR-NUM"
    FROM "SALESLIST"

    In the above example, the underlined "SALESLIST"."PUR-CODE" references the outer query column.

    When the SELECT statement above is executed, the search result is as follows.

    [Figure]

To Page Top

(4) Examples

Example 1 (LIMIT clause in a query specification)

This example searches the table of branch stores (BRANCHESLIST) for the ten branches with the highest sales revenues (SALES).

SELECT "BRANCH-CODE","RGN-CODE","BRANCH-NAME","SALES"
    FROM "BRANCHESLIST"
    ORDER BY "SALES" DESC
    LIMIT 10

The underlined portion indicates the LIMIT clause.

Example 2 (offset)

This example searches the table of branch stores (BRANCHESLIST) for the branches in positions 21 through 30 in terms of sales (SALES).

SELECT "BRANCH-CODE","RGN-CODE","BRANCH-NAME","SALES"
    FROM "BRANCHESLIST"
    ORDER BY "SALES" DESC
    LIMIT 20,10

The underlined portion indicates the LIMIT clause.

Example 3 (LIMIT clause in a derived table)

This example retrieves 100 rows from the sales history table (SALESLIST), and then calculates the total quantity purchased (PUR-NUM) for each product code (PUR-CODE) in those results.

SELECT "PUR-CODE",SUM("PUR-NUM")
    FROM (SELECT * FROM "SALESLIST" LIMIT 100) "SALESLIST"
        GROUP BY "PUR-CODE"

The underlined portion indicates the LIMIT clause.

The SELECT statement above retrieves an arbitrary set of 100 rows from the sales history table (SALESLIST), and then determines the results based on them. Because a different set of 100 rows can be retrieved each time it is executed, the SELECT statement above can produce different results every time it is executed.

Example 4 (LIMIT clause in a derived table)

This example specifies a condition on the date of purchase (PUR-DATE) in the sales history table (SALESLIST), and then counts the number of rows in the retrieval results. Because the LIMIT clause is specified, retrieval stops once the number of rows in the derived table reaches 1,000, and the retrieval results are returned.

SELECT COUNT(*)
  FROM (SELECT 1 FROM "SALESLIST"
        WHERE "PUR-DATE" BETWEEN ? AND ? LIMIT 1000) "SALESLIST"("PUR-DATE")

The underlined portion indicates the LIMIT clause.

By specifying a LIMIT clause in a derived table (by fixing the maximum number of rows in a derived table), you are limiting the execution time of the SELECT statement. This is useful when you are executing the above SELECT statement in order to progressively narrow the search results until you obtain fewer than 1,000 retrieval results. When the execution result is 1,000, it means there are at least 1,000 rows that satisfy the search condition. You can repeatedly execute the SELECT statement with different values for the dynamic parameters until you get fewer than 1,000 retrieval results.

Example 5 (LIMIT clause in a scalar subquery)

This example searches the sales history table (SALESLIST) for the date on which the greatest quantity purchased (PUR-NUM) occurred, and returns the corresponding date of purchase (PUR-DATE) and product code (PUR-CODE).

SELECT DISTINCT "PUR-DATE","PUR-CODE"
  FROM "SALESLIST"
    WHERE "PUR-DATE"=(SELECT "PUR-DATE" FROM "SALESLIST"
                        WHERE "PUR-NUM"=(SELECT MAX("PUR-NUM")
                                           FROM "SALESLIST") LIMIT 1)

The underlined portion indicates the LIMIT clause.

If there is more than one date with the maximum quantity purchased (PUR-NUM), the returned date of purchase (PUR-DATE) is selected randomly for the retrieval result, which means the SELECT statement above can produce different results every time it is executed.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2026, Hitachi, Ltd.