LIMIT, OFFSET, TOP & FETCH

A query against a real table can return millions of rows — but a web page shows ten at a time, a "top sellers" widget shows five, and a sample preview shows three. LIMIT and its cousins are how you ask for just a slice of the results.

Learn LIMIT, OFFSET, TOP & FETCH in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick recall.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You'll learn to cap rows, skip rows for pagination, and write the same idea in every major dialect — LIMIT , TOP , and the ANSI-standard FETCH FIRST .

A small blog. We'll slice it different ways. Imagine the real table has thousands of rows — LIMIT is what keeps your page fast.

LIMIT n tells the database "stop after n rows". Combined with ORDER BY , it answers "top N" questions: the 3 highest-scoring articles, the 10 newest orders, the 5 cheapest products.

A buffet has 200 dishes, but your plate holds 3. ORDER BY score DESC LIMIT 3 is "give me the 3 best, please" — order first, then take the top off.

OFFSET n skips the first n rows, then LIMIT counts from there. This is the heart of pagination: page 1 skips 0, page 2 skips one page's worth, and so on.

LIMIT 3 OFFSET 3 means "ignore the first 3, then give me the next 3" — exactly page 2 of a 3-per-page list.

Every "Next page" button you've clicked is powered by this formula. The page size stays constant in LIMIT ; the page number drives OFFSET .

Fill in the page size and the offset to return rows 6–10 of a lowest-score-first list.

Not every database speaks LIMIT . SQL Server historically uses TOP ; the ANSI SQL standard uses OFFSET … FETCH FIRST … ROWS ONLY . They cap rows the same way — only the spelling changes.

These lines are scrambled. Arrange them into a query that returns page 2 (5 per page) of articles, highest score first.

Why: clause order in SQL is fixed — SELECT , then FROM , then ORDER BY , and finally LIMIT … OFFSET . The sort must happen before the slice, otherwise "page 2 of the highest scores" would be meaningless.

Predict the result first, then reveal. Use the articles table.

One row: "Postgres Performance" (980) — the single highest score.

OFFSET 6 — because (4 − 1) × 2 = 6. That returns rows 7 and 8.

1 row — "Getting Started" (300). OFFSET 5 skips the first five, and only one row remains, so LIMIT 10 simply returns what's left.

The homepage wants the two most recent articles. Write the query — the expected shape is in the comments.

Practice quiz

What does LIMIT n do?

  • Skips the first n rows
  • Sorts the rows
  • Caps the number of rows returned to n
  • Returns the nth row only

Answer: Caps the number of rows returned to n. LIMIT n tells the database to stop after n rows.

What does OFFSET m do?

  • Skips the first m rows before returning results
  • Limits results to m rows
  • Sorts by column m
  • Repeats m rows

Answer: Skips the first m rows before returning results. OFFSET m skips m rows first; LIMIT then counts from there.

Why should LIMIT almost always be paired with ORDER BY?

  • ORDER BY is required syntax for LIMIT
  • It makes the query faster
  • LIMIT ignores rows otherwise
  • Without a sort, 'the top N' is undefined and may change between runs

Answer: Without a sort, 'the top N' is undefined and may change between runs. Without ORDER BY the database can return any rows in any order, so the slice is meaningless.

What is the pagination formula for OFFSET?

  • OFFSET = page * page_size
  • OFFSET = (page - 1) * page_size
  • OFFSET = page_size / page
  • OFFSET = page + page_size

Answer: OFFSET = (page - 1) * page_size. Page 1 is OFFSET 0, page 2 is OFFSET page_size, and so on: (page - 1) * page_size.

With page_size = 5, what OFFSET fetches page 3?

  • 10
  • 15
  • 5
  • 3

Answer: 10. (3 - 1) * 5 = 10.

OFFSET counts starting from which number?

  • 1
  • -1
  • 0
  • The page size

Answer: 0. OFFSET is zero-based; to skip the first 10 rows you write OFFSET 10.

Why is a very large OFFSET slow on a big table?

  • OFFSET re-sorts the table each time
  • The database still reads and discards every skipped row
  • Large OFFSET values are invalid
  • It duplicates the rows first

Answer: The database still reads and discards every skipped row. OFFSET must read past all skipped rows; deep pagination is better served by keyset pagination.

Where does LIMIT go in a MySQL/PostgreSQL query?

  • Right after SELECT
  • Before FROM
  • After WHERE but before SELECT
  • At the end of the query

Answer: At the end of the query. LIMIT goes at the end; SQL Server's TOP goes right after SELECT instead.

Which clause does SQL Server use to cap rows instead of LIMIT?

  • LIMIT
  • TOP
  • CAP
  • HEAD

Answer: TOP. SQL Server uses SELECT TOP n; the ANSI standard uses FETCH FIRST n ROWS ONLY.

What is the ANSI-standard way to return the first 3 rows?

  • LIMIT 3
  • TOP 3
  • FETCH FIRST 3 ROWS ONLY
  • ROWNUM <= 3

Answer: FETCH FIRST 3 ROWS ONLY. The SQL standard uses OFFSET ... FETCH FIRST n ROWS ONLY; LIMIT and TOP are vendor variants.