LIMIT
Cap the number of returned rows. Used during exploration to keep queries fast, and in production for pagination (with OFFSET). Without ORDER BY, which rows you get is engine- and plan-dependent — usually whichever happen to stream out of the scan first.
Syntax #
sql
SELECT *
FROM table_name
ORDER BY key
LIMIT 10 OFFSET 20;Dialect comparison #
| Engine | Syntax | Gotcha |
|---|---|---|
| PostgreSQL / MySQL / SQLite / DuckDB / Snowflake / Redshift / BigQuery | LIMIT n OFFSET m | always pair with ORDER BY; without one, "the first 10" is undefined |
| Oracle 12c+ / SQL Server 2012+ / DB2 / ANSI | OFFSET m ROWS FETCH FIRST n ROWS ONLY | ANSI-standard form; canonical on Oracle and modern SQL Server |
| SQL Server (idiomatic) | SELECT TOP n ... ORDER BY ... | no native OFFSET in the same syntax; use OFFSET ... FETCH for paging |
| Snowflake / BigQuery / DuckDB / Databricks | QUALIFY ROW_NUMBER() OVER (... ORDER BY ...) <= n | top-N-per-group without a subquery; not available on Postgres / MySQL |
Example #
Loading SQL editor...
Warning
LIMIT without ORDER BY returns arbitrary rows. If the query's intent is "show me any 10 orders" for exploration, that's fine. If the intent is "the top 10" or "the 10 most recent," the query needs ORDER BY with a tie-breaker — otherwise the "result" drifts across reruns as plans and statistics change.