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 specification or query expression body in a SELECT 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:

Specifies the offset of the first row to return from the retrieval results of the query expression. For example, if you specify LIMIT 10,5 (offset is 10, row-count is 5), processing skips the first 10 rows of the retrieval results of the query expression and retrieves rows 11 to 15.

The following rules apply:

  • The offset can only be specified in a LIMIT clause in the outermost query specification or query expression body in a SELECT statement. An offset is not allowed in a LIMIT clause in a derived table, scalar subquery, WITH clause, or CREATE VIEW statement.

  • The offset is expressed in the form of a value specification. For details about value specifications, see 7.21 Value specification.

  • An integer from 0 to 2147483647 (INTEGER type data) must be specified for offset.

  • Specifying 0 for offset is equivalent to not having an offset. In this case, the number of rows specified in row-count is retrieved starting from the first row of the results of the query expression.

  • If offset is a dynamic parameter, the assumed data type of the dynamic parameter will be INTEGER type.

  • You cannot specify the null value for offset.

row-count:

Specifies the maximum number of rows that will be retrieved from the results of a query expression or query expression body.

The following rules apply:

  • The maximum number of rows is specified in row-count, which is expressed in the form of a value specification. For details about value specifications, see 7.21 Value specification.

  • An integer from 0 to 2,147,483,647 (INTEGER type data) must be specified for row-count.

  • If row-count is 0, the number of retrieval results will be 0.

  • If row-count is a dynamic parameter, the assumed data type of the dynamic parameter will be INTEGER type.

  • You cannot specify the null value for row-count.

To Page Top

(3) Rules

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

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

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

  2. If the number of rows in the results of the query expression 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 for specifying a LIMIT clause in a derived table, scalar subquery, WITH clause, or CREATE VIEW statement

  1. A LIMIT clause is not permitted in a derived table of the following type:

    • 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 a LIMIT clause is specified, the number of rows in the results of the query expression body will be the following:

    MIN(number of rows in results of query expression body when no LIMIT clause is specified, row-count)

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

    • When the LIMIT clause is specified in a derived table, scalar subquery, or WITH clause (because the ORDER BY clause is not permitted in these cases)

    • When retrieving from a viewed table defined by a CREATE VIEW statement in which the LIMIT clause is specified (because the ORDER BY clause is not permitted in a CREATE VIEW statement)

    Example:

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

    When you execute the above SELECT statement, the retrieval results are not uniquely determined, as illustrated in the following figure:

    [Figure]

    Only two retrieval results are shown above, but other results might also appear.

  4. When a LIMIT clause is specified in a query expression body that references a column in an outer query, the LIMIT clause does not apply to the total number of rows of results from that query expression body. Rather, the LIMIT clause applies to the number of rows of query expression body results for a single value of the outer query column.

    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 portion "SALESLIST"."PUR-CODE" references a column in an outer query.

    When you execute the above SELECT statement, the retrieval results will be 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, 2020, Hitachi, Ltd.