ORDER BY, LIMIT and OFFSET

ORDER BY

Sorts the result set. ASC (ascending) is the default; use DESC for descending.

SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary DESC;

Sort by multiple columns — later columns break ties in the earlier ones:

SELECT * FROM employees ORDER BY department ASC, salary DESC;
-- grouped by department, and within each department, highest salary first

LIMIT

Caps the number of rows returned.

SELECT * FROM employees ORDER BY salary DESC LIMIT 5;
-- the 5 highest-paid employees

OFFSET

Skips a number of rows before starting to return results — combine it with LIMIT for pagination.

SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;
-- rows 21-30 ("page 3" at 10 rows per page)

Standard Pagination Pattern

-- page 1: LIMIT 10 OFFSET 0
-- page 2: LIMIT 10 OFFSET 10
-- page 3: LIMIT 10 OFFSET 20

SELECT * FROM employees
ORDER BY id
LIMIT 10 OFFSET (page_number - 1) * 10;

Common Errors & Troubleshooting

• Rows appearing in a different order on each run — a query with no ORDER BY has no guaranteed order at all, even if it happens to look consistent in testing; always add ORDER BY for pagination or any order-dependent logic.
• LIMIT/OFFSET returning duplicate or skipped rows across pages — this happens when new rows are inserted/deleted between fetching page 1 and page 2, shifting what OFFSET lands on; for frequently-changing data, cursor-based pagination (filtering by the last seen value) avoids this.
• ERROR: syntax error at or near "OFFSET" — LIMIT must come before OFFSET in the statement; check the clause order if you get this.

Key Takeaways

• ORDER BY decides the sequence; LIMIT/OFFSET decide the "window" of results — same idea as MongoDB's sort()/limit()/skip().
• Always pair LIMIT/OFFSET with an ORDER BY — without a defined order, which rows you get back isn't guaranteed to be consistent between runs.
• OFFSET gets slower on very large tables since Postgres still has to scan past the skipped rows — cursor-based pagination (using a WHERE id > last_seen_id) scales better for huge datasets.