SQL Academy · Lesson

LIMIT and OFFSET

Fetch the first N rows and paginate.

LIMIT and OFFSET is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Fetching Just a Few Rows

Sometimes you don't want every matching row, just the first handful. LIMIT caps how many rows the query returns.

This returns at most 5 products.

SELECT name, price
FROM products
LIMIT 5;

LIMIT Without ORDER BY Is Random

LIMIT on its own gives you some rows, but which rows is undefined. Always pair it with ORDER BY so the result is meaningful and repeatable.

-- The 5 newest orders, deterministically
SELECT id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 5;

Skipping Rows with OFFSET

OFFSET tells the database to skip a number of rows before returning. Combined with LIMIT, it's the basis of pagination.

This skips the first 10 rows and returns the next 5.

SELECT name, price
FROM products
ORDER BY name
LIMIT 5 OFFSET 10;

Building Pages

For a page size of 10, page numbers map to offsets like this:

Page 1: OFFSET 0
Page 2: OFFSET 10
Page 3: OFFSET 20

The formula is OFFSET = (page - 1) * page_size.

-- Page 3 with 10 rows per page
SELECT name, price
FROM products
ORDER BY name
LIMIT 10 OFFSET 20;

Order of Operations

The database applies the clauses in this order: filter with WHERE, sort with ORDER BY, then trim with OFFSET and LIMIT.

So you always page through the sorted, filtered result.

SELECT name, price
FROM products
WHERE price > 0
ORDER BY price DESC
LIMIT 10 OFFSET 0;

LIMIT ALL and OFFSET 0

LIMIT ALL means "no limit" (the same as omitting LIMIT), and OFFSET 0 skips nothing. They're occasionally useful when a value is built dynamically.

SELECT name
FROM products
ORDER BY name
LIMIT ALL OFFSET 0;

The Standard FETCH Syntax

PostgreSQL also supports the SQL-standard form: OFFSET n ROWS FETCH NEXT m ROWS ONLY. It does exactly what LIMIT/OFFSET do, just more verbosely.

SELECT name, price
FROM products
ORDER BY name
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Why Large OFFSETs Are Slow

A big OFFSET still makes the database scan and discard every skipped row. OFFSET 100000 reads 100,000 rows just to throw them away, which gets slow on deep pages.

-- Reads 100000 rows then discards them
SELECT id, name
FROM products
ORDER BY id
LIMIT 10 OFFSET 100000;

Keyset Pagination

A faster alternative is keyset (seek) pagination. Instead of an offset, remember the last row you saw and ask for rows after it.

This jumps straight to the next page using an index, no skipping.

-- Next page after the last seen id (e.g. 5000)
SELECT id, name
FROM products
WHERE id > 5000
ORDER BY id
LIMIT 10;

Stable Pages Need a Stable Sort

If your ORDER BY has ties, rows can shuffle between page loads and you may see duplicates or gaps. End the sort with a unique key (like the primary key) so paging is consistent.

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

A Single Top Row

LIMIT 1 is a quick way to grab a single row, like the most expensive product or the latest order. Combine it with the right ORDER BY.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 1;

Quick Check

You show 10 rows per page. Which clause returns page 3?

Recap

You can now slice your results:

LIMIT n caps the number of rows
OFFSET m skips rows; OFFSET = (page-1)*size
Always combine with ORDER BY for predictable pages
Prefer keyset pagination over large offsets for speed

Next, you'll use these tools to find Top-N records.

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

Frequently asked questions

Is the “LIMIT and OFFSET” lesson free?

Yes — the full text of “LIMIT and OFFSET” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “LIMIT and OFFSET”?

Fetch the first N rows and paginate. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LIMIT and OFFSET” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy