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.