Limiting results: the biggest dialect split
Returning just the top N rows is where SQL dialects diverge the most. Compare LIMIT, TOP and FETCH FIRST across every database, plus MongoDB's limit().
"Give me the 10 most recent hires" sounds simple, but restricting the number of rows is the single most fragmented feature in SQL. Three different syntaxes are in play — LIMIT, TOP and the ANSI FETCH FIRST — and knowing which belongs to which engine saves a lot of debugging.
One rule holds everywhere: if you want a *predictable* top-N, you must ORDER BY first. Without ordering, the database is free to return any rows it likes.
Top 10 most recent hires
Return the 10 newest employees
Notice how the limiting clause moves around the statement — and even into the select list for SQL Server.
SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 10;PostgreSQL also supports the ANSI FETCH FIRST 10 ROWS ONLY, but LIMIT is the idiomatic form.
SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 10;MySQL and MariaDB use LIMIT offset, count or LIMIT count OFFSET offset for paging.
SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 10;SELECT TOP 10 *
FROM employees
ORDER BY hire_date DESC;TOP goes in the select list. SQL Server 2012+ also supports OFFSET ... FETCH for paging.
SELECT *
FROM employees
ORDER BY hire_date DESC
FETCH FIRST 10 ROWS ONLY;FETCH FIRST requires Oracle 12c or newer. Older versions used the ROWNUM pseudo-column in a subquery.
SELECT *
FROM employees
ORDER BY hire_date DESC
FETCH FIRST 10 ROWS ONLY;No direct equivalent
This database does not support this feature with the same syntax. See the notes for alternatives.
Paging: skipping rows
To fetch the *next* page you skip rows as well as limit them. The ANSI form (OFFSET ... FETCH) is now supported by PostgreSQL, SQL Server, Oracle and Db2, while MySQL/MariaDB keep the older LIMIT offset, count.
Top-N in MongoDB
The 10 newest employees as documents
SQL (relational)
SELECT *
FROM employees
ORDER BY hire_date DESC
LIMIT 10;MongoDB (document)
db.employees
.find({})
.sort({ hire_date: -1 })
.limit(10);MongoDB chains cursor methods: sort({ hire_date: -1 }) is your ORDER BY ... DESC (-1 = descending) and limit(10) is your LIMIT. Skipping rows for paging uses .skip(n).