ORDER BY, LIMIT, and OFFSET Clauses

Last Updated: June 12, 2026 • 15 min read

1. Introduction

The ORDER BY, LIMIT, and OFFSET clauses control how results are sorted and how many rows are returned. Together, they enable pagination — a critical feature for any application displaying lists of data.

2. Why It Matters

Pagination queries with LIMIT/OFFSET are frequently tested in interviews. Every web application and API needs pagination. Understanding the performance implications of deep OFFSET pagination — and knowing keyset pagination as the alternative — separates junior developers from senior engineers.

3. Real-World Analogy

Pagination is like browsing a library catalog. ORDER BY = how the books are sorted on the shelf (by title, by date). LIMIT = how many books you can carry at once (10 per page). OFFSET = how many shelves you skip before you start picking. OFFSET 1000 means you walked past 1000 shelves just to get to the right one — obviously slow. Keyset pagination is like bookmarking where you left off.

4. How It Works

ORDER BY: Sorts results by one or more columns. ASC (default) or DESC. Supports multiple columns, expressions, and NULLS FIRST/LAST control.
LIMIT n: Return at most n rows.
OFFSET m: Skip the first m rows. Combined: LIMIT 10 OFFSET 20 = page 3 with 10 per page.

Pagination formula: Page n = LIMIT page_size OFFSET (n-1) * page_size

5. Internal Architecture

The OFFSET performance problem: OFFSET 1000000 LIMIT 10 forces the database to read, sort, and discard 1,000,000 rows just to return 10. Keyset pagination replaces OFFSET with a WHERE clause on the last-seen value, achieving O(log n) via index seek.

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Common Pitfall

Using OFFSET without a unique tiebreaker in ORDER BY. If two rows have identical sort values, they may appear on different pages or be skipped entirely when data changes between page loads.

Interview Insight

When asked about pagination in system design interviews, always mention the OFFSET performance problem and keyset pagination as the solution. This demonstrates production-level thinking.

Not indexing ORDER BY columns: Sorting millions of rows without an index forces a full sort operation.
OFFSET for infinite scroll: Keyset pagination is the correct approach — OFFSET degrades linearly with each page.

9. Quick Quiz

Q1: What's the OFFSET for page 7 with 25 items per page?

OFFSET = (7-1) * 25 = 150. Query: LIMIT 25 OFFSET 150.

Q2: Why should you include a unique column in ORDER BY for pagination?

Without a unique tiebreaker (like id), rows with duplicate sort values may shift between pages when data changes, causing missing or duplicate results.

10. Scenario-Based Challenge

Scenario

Your social media app has 50 million posts. Users scroll through an infinite feed. The current OFFSET-based pagination takes 8 seconds on page 10,000. How would you redesign it using keyset pagination? What index would you need?

11. Debugging Exercise

This pagination query returns inconsistent results between page loads:

Fix: Add a unique tiebreaker: ORDER BY created_at DESC, id DESC. Without it, posts with the same created_at can appear in random order, causing duplicates or missing items between pages.

12. Interview Questions

Q: Why is OFFSET slow for deep pagination?

OFFSET N requires the database to read, sort, and discard N rows before returning results. Page 100,000 reads ~1M rows. Keyset pagination uses an index seek — O(log n) regardless of page depth.

Q: When can you NOT use keyset pagination?

When users need to jump to arbitrary pages (e.g., "go to page 50"). Keyset only supports sequential navigation. Use OFFSET with a maximum page limit for admin dashboards.

Q: What does NULLS FIRST vs NULLS LAST do?

Controls where NULL values appear in sorted results. Default: NULLS LAST for ASC, NULLS FIRST for DESC. Use explicit NULLS placement for consistent cross-database behavior.

13. Production Considerations

Index ORDER BY columns: Create composite indexes matching your sort order: CREATE INDEX idx ON orders(created_at DESC, id DESC);
Cap maximum page depth: Limit OFFSET to a reasonable maximum (e.g., 10,000) to prevent runaway queries. Show "use filters to narrow results" for deeper pages.
Cursor-based APIs: For REST/GraphQL APIs, return a cursor (base64-encoded last-seen values) instead of page numbers. This naturally leads to keyset pagination.
FETCH FIRST: The SQL standard equivalent of LIMIT: FETCH FIRST 10 ROWS ONLY. Works across PostgreSQL, Oracle, and DB2.