LIMIT Clause

Specifies the maximum number of result set rows to return.

Syntax

LIMIT { rows | ALL }

Parameters

rows

The maximum number of rows to return.

ALL

(default) Returns all rows.

Dependencies

  • Use an ORDER BY clause with LIMIT. Otherwise, the query returns an undefined subset of the result set. For example, the following SELECT statement omits ORDER BY. Successive iterations of this query are liable to return a different set of five records from the customer_dimension table:
    => SELECT customer_name, customer_city FROM customer_dimension LIMIT 5;
       customer_name   | customer_city
    -------------------+---------------
     Craig S. Robinson | Fayetteville
     Mark M. Kramer    | Joliet
     Barbara S. Farmer | Alexandria
     Julie S. McNulty  | Grand Prairie
     Meghan R. Garcia  | Athens
    (5 rows)
    

    In contrast, the following SELECT statement includes an ORDER BY clause and returns a consistent set of results:

    => SELECT customer_name, customer_city FROM customer_dimension 
       ORDER BY customer_city, customer_name LIMIT 5;
         customer_name     | customer_city
    -----------------------+---------------
     Alexander . Dobisz    | Abilene
     Alexander B. McCabe   | Abilene
     Alexander J. Goldberg | Abilene
     Alexander M. Fortin   | Abilene
     Alexander P. Moore    | Abilene
    (5 rows)
    
  • LIMIT must follow the SELECT statement's ORDER BY clause.
  • When a SELECT statement specifies both LIMIT and OFFSET, Vertica first processes the OFFSET statement, and then applies the LIMIT statement to the remaining rows.