SQL LIMIT and OFFSET: Paginate Your Query Results Like a Pro

Every app that shows lists of data — a product catalog, a blog feed, a leaderboard — needs pagination. And at the heart of SQL-based pagination sit two deceptively simple keywords: LIMIT and OFFSET. If you've ever wondered how websites show you "Page 1 of 47" or a "Load more" button, this is the mechanism behind it.

In this guide we'll cover exactly what LIMIT and OFFSET do, how to use them correctly, and — just as importantly — where they can bite you when your dataset grows.

What Problem Do LIMIT and OFFSET Solve?

Imagine your products table has 50,000 rows. If you run a bare SELECT * FROM products, your database has to send all 50,000 rows to your application. That's slow, wastes memory, and overwhelms users who only want to see the first 20 items.

LIMIT caps how many rows the database returns. OFFSET tells it where to start.

Basic Syntax

SELECT column1, column2
FROM table_name
ORDER BY some_column
LIMIT number_of_rows
OFFSET rows_to_skip;

Note: MySQL also supports a shorthand: LIMIT offset, count — but the two-argument form can be confusing because the order is reversed from what you might expect. Using the explicit LIMIT … OFFSET … form is clearer and works in PostgreSQL, SQLite, and MySQL alike.

Example: A Simple Product Catalog

Let's say we have this table:

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100),
    category    VARCHAR(50),
    price       NUMERIC(10, 2),
    created_at  TIMESTAMP DEFAULT NOW()
);

Get the first 10 products, sorted by price:

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10;

Get the second page (rows 11–20):

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10
OFFSET 10;

Get the third page (rows 21–30):

SELECT id, name, category, price
FROM products
ORDER BY price ASC
LIMIT 10
OFFSET 20;

The pattern is simple: OFFSET = (page_number - 1) * page_size.

Real-World Example: Blog Post Feed

Here's what this looks like in a blog application with a posts table:

CREATE TABLE posts (
    id         SERIAL PRIMARY KEY,
    title      VARCHAR(200),
    author_id  INT,
    published  BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW()
);

Fetch page 3 of published posts, 5 per page, newest first:

SELECT id, title, created_at
FROM posts
WHERE published = TRUE
ORDER BY created_at DESC
LIMIT 5
OFFSET 10;

You can even make this dynamic. In application code you'd pass the page number as a parameter:

-- Page number: 5, Page size: 20
SELECT id, title, created_at
FROM posts
WHERE published = TRUE
ORDER BY created_at DESC
LIMIT 20
OFFSET 80;   -- (5 - 1) * 20 = 80

Common Mistakes and Gotchas

1. Forgetting ORDER BY

This is the most important rule: always pair LIMIT/OFFSET with ORDER BY.

Without ORDER BY, the database can return rows in any order — and that order can change between queries. Your "page 2" might contain rows that also appeared on "page 1".

-- ❌ BAD — results are unpredictable
SELECT * FROM products LIMIT 10 OFFSET 10;

-- ✅ GOOD — deterministic ordering
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

2. OFFSET Doesn't Skip Work — It Just Throws It Away

Here's the performance trap that surprises many developers: OFFSET 10000 doesn't mean the database starts reading from row 10,001. It means the database reads all 10,001 rows and then discards the first 10,000 before returning results to you.

For small datasets this is fine. For large datasets this becomes very slow:

-- This query reads and discards 990,000 rows, then returns 10
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 990000;

For most beginner use cases (tables with a few thousand rows), this is not a problem. But it's good to know for when your app grows.

3. LIMIT Without a Number Is Invalid in Standard SQL

-- ❌ INVALID — can't use OFFSET without LIMIT in most databases
SELECT * FROM products OFFSET 5;

-- ✅ Use a very large number if you want "all rows starting from offset"
SELECT * FROM products ORDER BY id OFFSET 5 LIMIT ALL;  -- PostgreSQL only

4. Counting Total Pages Requires a Separate Query

LIMIT and OFFSET only return a slice of data. To show "Page 3 of 47", you need a separate count:

-- Get total count for pagination controls
SELECT COUNT(*) AS total_posts
FROM posts
WHERE published = TRUE;

-- Then compute: total_pages = CEIL(total_posts / page_size)

Putting It All Together: A Complete Pagination Example

Here's a realistic scenario: a comments section on a news site, showing 10 comments per page.

-- Schema
CREATE TABLE comments (
    id         SERIAL PRIMARY KEY,
    post_id    INT NOT NULL,
    author     VARCHAR(100),
    body       TEXT,
    upvotes    INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Page 1: top-voted comments on post #42
SELECT id, author, body, upvotes
FROM comments
WHERE post_id = 42
ORDER BY upvotes DESC, created_at DESC
LIMIT 10
OFFSET 0;

-- Page 2
SELECT id, author, body, upvotes
FROM comments
WHERE post_id = 42
ORDER BY upvotes DESC, created_at DESC
LIMIT 10
OFFSET 10;

Notice the double ORDER BY — we sort by votes first (so the best comments appear first), then by date as a tiebreaker. This makes results stable and predictable.

Quick Reference

Key Takeaways

• LIMIT controls how many rows are returned. OFFSET controls where in the result set to start.
• Always use ORDER BY with LIMIT/OFFSET — without it, results are non-deterministic.
• The formula for page-based pagination is OFFSET = (page - 1) * page_size.
• OFFSET can become slow on very large datasets because the database still reads (and discards) all skipped rows — but for most beginner use cases, this won't be an issue.
• Always run a separate COUNT(*) query to know the total number of pages.

LIMIT and OFFSET are among the first tools you'll reach for when building real applications with SQL. They're simple, powerful, and supported by virtually every relational database out there.

Have you hit any pagination bugs in your own projects? Drop them in the comments — I'd love to hear what tripped you up and how you solved it! 🚀