LIMIT, OFFSET and FETCH FIRST
Pagination syntax across MySQL, Postgres, and SQL Server.
Pagination Is a Cross-Dialect Question
"How would you fetch the first 10 rows?" sounds trivial until the interviewer adds "and do it in standard SQL" or "now in SQL Server." The syntax for limiting rows is one of the most fragmented features across databases.
This lesson maps the three main forms: LIMIT (MySQL/Postgres/SQLite), OFFSET ... FETCH (ANSI standard, SQL Server), and the older TOP (SQL Server).
LIMIT in MySQL and PostgreSQL
The most common form is LIMIT n, which caps the result at n rows. It is supported by MySQL, PostgreSQL, and SQLite.
Crucially, LIMIT is applied after ORDER BY in logical execution order, so you almost always pair them. Limiting without sorting gives you an arbitrary set of rows.
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;All lessons in this course
- Multi-Column Sorting and NULL Placement
- LIMIT, OFFSET and FETCH FIRST
- Returning the Top-N Rows Reliably
- Sorting by Expressions and Aliases