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.
LIMIT { num-rows OVER ( PARTITION BY partition-expr[,...] ORDER BY order-expr[,...] ) }
| ALL }
Arguments
num-rows- The maximum number of rows to return.
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.
partition-expr- Column or expression returning a column to partition results by.
order-expr[ ASC | DESC ] [ NULLS { FIRST | LAST } ]- Column or expression returning the column to order results by. Each column can be sorted in ascending (default) or descending order, with nulls first or last.
ALL- Returns all rows, valid only when LIMIT is applied to the entire result set.
Limiting returned rows
LIMIT returns the specified number of rows from the queried dataset. Row precedence is determined by the query's ORDER BY clause. If you do not use an ORDER BY clause, the query returns an undefined subset of the result set.
When a SELECT statement specifies both LIMIT and OFFSET, Vertica first processes the OFFSET, and then applies LIMIT to the remaining rows.
The following query returns the first 10 rows of data, as ordered first by region and then by 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.
For example, the following query specifies window partitioning on the result set. LIMIT is set to 2, so each window partition can return no more than two rows. The OVER clause partitions the result set by store_region, so in each region the query returns 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)