LIMIT queries
A query can include a LIMIT clause to limit its result set in two ways:
-
Return a subset of rows from the entire result set.
-
Set window partitions on the result set and limit the number of rows in each window.
Limiting the query result set
Queries that use the LIMIT clause with ORDER BY return a specific subset of rows from the queried dataset. Vertica processes these queries efficiently using Top-K optimization, which is a database query ranking process. Top-K optimization avoids sorting (and potentially writing to disk) an entire data set to find a small number of rows. This can significantly improve query performance.
For example, the following query returns the first 20 rows of data in table customer_dimension, as ordered by number_of_employees:
=> SELECT store_region, store_city||', '||store_state location, store_name, number_of_employees
FROM store.store_dimension ORDER BY number_of_employees DESC LIMIT 20;
store_region | location | store_name | number_of_employees
--------------+----------------------+------------+---------------------
East | Nashville, TN | Store141 | 50
East | Manchester, NH | Store225 | 50
East | Portsmouth, VA | Store169 | 50
SouthWest | Fort Collins, CO | Store116 | 50
SouthWest | Phoenix, AZ | Store232 | 50
South | Savannah, GA | Store201 | 50
South | Carrollton, TX | Store8 | 50
West | Rancho Cucamonga, CA | Store102 | 50
MidWest | Lansing, MI | Store105 | 50
West | Provo, UT | Store73 | 50
East | Washington, DC | Store180 | 49
MidWest | Sioux Falls, SD | Store45 | 49
NorthWest | Seattle, WA | Store241 | 49
SouthWest | Las Vegas, NV | Store104 | 49
West | El Monte, CA | Store100 | 49
SouthWest | Fort Collins, CO | Store20 | 49
East | Lowell, MA | Store57 | 48
SouthWest | Arvada, CO | Store188 | 48
MidWest | Joliet, IL | Store82 | 48
West | Berkeley, CA | Store248 | 48
(20 rows)
Important
If a LIMIT clause omitsORDER BY, results can be nondeterministic.
Limiting window partitioning results
You can use LIMIT to set 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 includes a LIMIT clause that specifies window partitioning. In this case, Vertica partitions the result set by store_region, where each partition window displays for one region the two stores with the fewest 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)