LIMIT/OFFSET
LIMIT caps the number of rows and OFFSET skips rows, which together give pagination.
In this page:
Syntax
SELECT columns
FROM table_name
ORDER BY column
LIMIT n OFFSET m;LIMIT/OFFSET
LIMIT n returns at most n rows and OFFSET m skips the first m. Always pair them with ORDER BY so pages are stable.
Large offsets are slow because the database still walks the skipped rows; keyset pagination (WHERE id > last_id) scales better.
PostgreSQL also supports FETCH FIRST n ROWS ONLY.
Note:
Keyset pagination beats OFFSET on big tables.
Example: LIMIT/OFFSET
CREATE TABLE items (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO items VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'g');
SELECT label FROM items ORDER BY id LIMIT 3;
SELECT label FROM items ORDER BY id LIMIT 3 OFFSET 3;
SELECT label FROM items ORDER BY id DESC LIMIT 2;
-- Output:
-- label
-- a
-- b
-- c
-- label
-- d
-- e
-- f
-- label
-- g
-- fLogin to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Paging without ORDER BY
- Huge OFFSET values
- Forgetting LIMIT applies after sorting
Chapter Summary
- LIMIT caps rows
- OFFSET skips rows
- Use ORDER BY for stable pages
- Keyset pagination scales better
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: