LIMIT Clause
Specifies the maximum number of result set rows to return, either from the entire result set, or from windows of a partitioned result set.
Syntax
Applied to entire result set:
LIMIT { num-rows | ALL }
Applied to windows of a partitioned result set:
LIMIT num‑rows OVER ( PARTITION BY column‑expr‑x, ORDER BY column‑expr‑y [ASC | DESC]
Parameters
| num‑rows | The maximum number of rows to return. |
ALL
|
Returns all rows, valid only when LIMIT is applied to the entire result set. |
OVER()
|
Specifies how to partition and sort input data with respect to the current row. The input data is the result set that the query returns after it evaluates FROM, WHERE, GROUP BY, and HAVING clauses. For details, see Using LIMIT with Window Partitioning below. |
Limiting Returned Rows
LIMIT specifies to return only top-k rows from the queried dataset. Row precedence is determined by the query's ORDER BY clause.
The following dependencies apply:
- Always use an ORDER BY clause with LIMIT. Otherwise, the query returns an undefined subset of the result set. The ORDER BY clause must precede LIMIT.
- When a SELECT statement specifies both LIMIT and OFFSET, Vertica first processes the OFFSET, and then applies LIMIT to the remaining rows.
For example, the following query returns the first 10 rows of data in table customer_dimension, as ordered by columns store_region and number_of_employees:
=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees
FROM store.store_dimension WHERE number_of_employees <= 12 ORDER BY store_region, number_of_employees LIMIT 10;
store_region | location | store_name | number_of_employees
--------------+----------------+------------+---------------------
East | Stamford, CT | Store219 | 12
East | New Haven, CT | Store66 | 12
East | New York, NY | Store122 | 12
MidWest | South Bend, IN | Store134 | 10
MidWest | Evansville, IN | Store30 | 11
MidWest | Green Bay, WI | Store27 | 12
South | Mesquite, TX | Store124 | 10
South | Cape Coral, FL | Store18 | 11
South | Beaumont, TX | Store226 | 11
South | Houston, TX | Store33 | 11
(10 rows)
Using LIMIT with Window Partitioning
You can use LIMIT to apply window partitioning on query results, and limit the number of rows that are returned in each window:
SELECT ... FROM dataset LIMIT num‑rows OVER ( PARTITION BY column‑expr‑x, ORDER BY column‑expr‑y [ASC | DESC] )
where querying dataset returns num‑rows rows in each column‑expr‑x partition with the highest or lowest values of column‑expr‑y.
For example, the following statement queries table store.store_dimension and specifies window partitioning on the result set. LIMIT is set to 2, so each window partition can display no more than two rows. The OVER clause specifies to partition the result set by store_region, where each partition window displays for one region the two stores with the smallest number of employees:
=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees FROM store.store_dimension
LIMIT 2 OVER (PARTITION BY store_region ORDER BY number_of_employees ASC);
store_region | location | store_name | number_of_employees
--------------+---------------------+------------+---------------------
West | Norwalk, CA | Store43 | 10
West | Lancaster, CA | Store95 | 11
East | Stamford, CT | Store219 | 12
East | New York, NY | Store122 | 12
SouthWest | North Las Vegas, NV | Store170 | 10
SouthWest | Phoenix, AZ | Store228 | 11
NorthWest | Bellevue, WA | Store200 | 19
NorthWest | Portland, OR | Store39 | 22
MidWest | South Bend, IN | Store134 | 10
MidWest | Evansville, IN | Store30 | 11
South | Mesquite, TX | Store124 | 10
South | Beaumont, TX | Store226 | 11
(12 rows)