OFFSET and LIMIT options specify how many rows to skip from the beginning, and the maximum number of rows to return by a SQL SELECT statement.
Quick Example:
-- Return next 10 books starting from 11th (pagination, show results 11-20) SELECT * FROM books ORDER BY name OFFSET 10 LIMIT 10;
Overview:
Syntax (full...) | SELECT … [LIMIT n | ALL] [OFFSET m] | |||
Ordering | Applied after ordering if ORDER BY is specified | |||
LIMIT | n | Return no more than n rows after skipping OFFSET m rows | ||
ALL | Return all rows after skipping m rows | The same as omitting LIMIT | ||
OFFSET | m | Skip first m rows after ordering | ||
0 | The same as omitting OFFSET | |||
SELECT INTO | LIMIT 1 is not required in SELECT INTO variable FROM … as only the first row is taken and the others are ignored if SELECT INTO STRICT is not specified |
|||
Alternative | SELECT … OFFSET m FETCH FIRST n ROWS ONLY |
Version: PostgreSQL 9.1
OFFSET and LIMIT options can be used to restrict the number of rows returned by the query or provide pagination (output by pages):
CREATE TABLE num (c1 INT); -- Insert 10 rows INSERT INTO num VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); -- Return first 3 rows SELECT * FROM num ORDER BY c1 LIMIT 3;
Both OFFSET and LIMIT applied after sorting. Return 7th and 8th rows:
SELECT * FROM num ORDER BY c1 DESC OFFSET 2 LIMIT 2; -- Result: -- 8 -- 7
Instead of LIMIT option you can use ANSI/ISO-compliant FETCH FIRST ROWS ONLY. Return 7th and 8th rows:
SELECT * FROM num ORDER BY c1 DESC OFFSET 2 FETCH FIRST 2 ROWS ONLY; -- Result: -- 8 -- 7
Limiting the number of rows in other databases:
MySQL:
LIMIT n | LIMIT ALL is not supported | |
OFFSET m | Must follow after LIMIT | |
Short Form | LIMIT m, n is equivalent to LIMIT n OFFSET m | |
FETCH FIRST ROWS ONLY |