Row limiting / paging (LIMIT/OFFSET, FETCH, ROWNUM, TOP)

Oracle

-- 12c+ standard: OFFSET/FETCH
SELECT name, salary FROM employee
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Older versions: with ROWNUM
SELECT * FROM (
  SELECT name, salary FROM employee ORDER BY salary DESC
) WHERE ROWNUM <= 10;

Used to limit/page through a result set. Since Oracle 12c the standard OFFSET n ROWS FETCH NEXT m ROWS ONLY is supported. Before that the ROWNUM pseudocolumn was used; because ROWNUM is assigned before sorting, wrapping an ORDER BY subquery is mandatory.

PostgreSQL

-- LIMIT/OFFSET
SELECT name, salary FROM employee
ORDER BY salary DESC
LIMIT 10 OFFSET 20;

-- Standard equivalent: FETCH
SELECT name, salary FROM employee
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

PostgreSQL supports both LIMIT n OFFSET m and the standard OFFSET ... FETCH form. Large OFFSET values are slow; for deep paging, key-based (keyset / 'WHERE id > last_id') pagination is more efficient. LIMIT without ORDER BY does not guarantee result order.

MySQL

-- LIMIT [offset,] row_count
SELECT name, salary FROM employee
ORDER BY salary DESC
LIMIT 10 OFFSET 20;

-- Short form: LIMIT offset, row_count
SELECT name, salary FROM employee ORDER BY salary DESC LIMIT 20, 10;

MySQL/MariaDB use the LIMIT keyword in two forms: LIMIT row_count OFFSET offset or LIMIT offset, row_count. MySQL 8.0.19+ also accepts the standard FETCH syntax. Keyset pagination is recommended for deep OFFSET performance.

SQL Server

-- First N with TOP
SELECT TOP 10 name, salary FROM employee ORDER BY salary DESC;

-- OFFSET/FETCH for paging (2012+), ORDER BY mandatory
SELECT name, salary FROM employee
ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SQL Server uses TOP n (with TOP (n) PERCENT / WITH TIES variants) for the first N rows, and since 2012 OFFSET ... FETCH for paging. ORDER BY is mandatory for OFFSET/FETCH to work. SQL Server has no LIMIT keyword.

MongoDB

// Paging with skip/limit
db.employee.find()
  .sort({ salary: -1 })
  .skip(20)
  .limit(10)

In MongoDB limit() sets the number of documents returned and skip() the number skipped; combined with sort() they provide paging. Large skip values are expensive; for deep paging a range (keyset) query based on the last seen value is preferred.

Redis

# Range read on a sorted set (ZSET):
ZREVRANGE salary_sorted 20 29    # members ranked 21-30
# Range on a list:
LRANGE queue 0 9              # first 10 elements

Redis has no SQL LIMIT, but it has range-read commands: ZRANGE/ZREVRANGE start stop (index based) or ZRANGEBYSCORE ... LIMIT offset count for Sorted Sets; LRANGE for Lists. These commands provide paging-like access.

Elasticsearch

GET employee/_search
{
  "from": 20,
  "size": 10,
  "sort": [ { "salary": "desc" } ]
}

In Elasticsearch 'size' sets the number of hits returned and 'from' the number skipped (the LIMIT/OFFSET equivalent). from+size can be at most index.max_result_window (default 10,000); for deep paging use 'search_after' (keyset) or a PIT (point-in-time).