LIMIT
The LIMIT clause restricts a query's result set to a maximum number of rows, often used with ORDER BY to fetch a top-N subset.
Overview
LIMIT caps the number of rows a query returns. It's typically the last clause evaluated in a SELECT statement, applied after ORDER BY, and is the standard way to implement "top N" queries, pagination, or quick previews of a large table.
Copy code
SELECT product_name, revenue
FROM sales
ORDER BY revenue DESC
LIMIT 10;
LIMIT without ORDER BY
Without an ORDER BY, the rows a LIMIT returns are not guaranteed to be deterministic -- the engine can return any subset of rows in any order, and that subset can even change between runs on the same data. Always pair LIMIT with ORDER BY when the specific rows returned matter.
Pagination with LIMIT and OFFSET
LIMIT is commonly combined with OFFSET to page through results:
Copy code
SELECT id, name
FROM customers
ORDER BY id
LIMIT 20 OFFSET 40; -- rows 41-60
DuckDB notes
DuckDB supports the standard LIMIT count OFFSET offset syntax, and also allows LIMIT and OFFSET to take arbitrary expressions, not just literal integers (e.g. LIMIT 2 + 2). For a random sample of rows instead of a deterministic top-N, DuckDB provides a separate USING SAMPLE clause:
Copy code
SELECT * FROM large_table
USING SAMPLE 10 PERCENT; -- random sample, a different mechanism than LIMIT
For large offsets, keep in mind that the database still has to compute and skip over the earlier rows, so very large OFFSET values on very large tables can be slower than filtering on an indexed/sorted key (a "keyset pagination" approach using WHERE id > :last_id LIMIT n).
Related terms
The OFFSET clause skips a specified number of rows in a query's result set before returning the remaining rows, commonly used for pagination alongside LIMIT.
ORDER BY clause →The ORDER BY clause is a fundamental SQL command that lets you control the sequence in which your query results are returned.
WHERE clause →The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.
ROW_NUMBER →ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.
SELECT statement →The SELECT statement is the workhorse of SQL, used to retrieve and transform data from tables, views, or other data sources.
OVER clause →The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.
FAQS
Only when combined with ORDER BY. Without an explicit sort order, the rows selected by LIMIT are implementation-defined and can vary between runs.
They do the same thing -- LIMIT is the ANSI SQL / PostgreSQL / DuckDB / MySQL syntax, while TOP is the SQL Server / older Sybase syntax for capping the number of returned rows.
Large OFFSET values force the database to skip over rows, which gets slower as the offset grows; keyset pagination (filtering on the last seen sort key with WHERE and a small LIMIT) scales much better than OFFSET-based pagination.
