LIMIT
SQL · Limiting Rows
Theory
LIMIT n tells the database to return at most n rows of the result. It is applied at the very end, after filtering, grouping and sorting, so ORDER BY salary DESC LIMIT 3 gives the three highest-paid employees. LIMIT n OFFSET m skips the first m rows and then returns n, which is the basic form of pagination.
Without ORDER BY, LIMIT returns an arbitrary set of rows, which may change between runs. LIMIT is not part of the SQL standard, so syntax varies: MySQL, PostgreSQL, SQLite and Snowflake use LIMIT; SQL Server uses SELECT TOP n; Oracle, Db2 and newer SQL Server/PostgreSQL support the standard FETCH FIRST n ROWS ONLY. Ties at the cut-off are broken arbitrarily unless you add a tie-breaker column to ORDER BY.
Why we use it
You rarely need every row: a top-10 list, a preview of a big table or one page of results is enough. LIMIT keeps output small, cuts network transfer and lets the engine stop early.
When to use it
Use LIMIT for top-N reports, quick previews and simple paging, always with ORDER BY when the choice of rows matters. Use TOP on SQL Server and FETCH FIRST for portable code. For deep pages, keyset paging (WHERE id > last_id) scales better than large OFFSETs.
How it works
- Query produces its full sorted result
- Is there an OFFSET m? Yes: Skip the first m rows. No: Start from the first row.
- Take rows one by one
- Have n rows been taken? Yes: Stop reading. No: Take the next row.
- Return at most n rows
Syntax
SELECT columns
FROM table
LIMIT n;Example
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;Key points
- Often paired with ORDER BY for top-N queries
- SQL Server uses TOP n instead of LIMIT
- Standard SQL form is FETCH FIRST n ROWS ONLY
- LIMIT n OFFSET m skips m rows first (simple paging)
- Without ORDER BY the chosen rows are arbitrary