LIMIT, OFFSET, TOP & FETCH

Reviewed & published by Brayan K

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.

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.

What You'll Learn

• Cap the number of rows with LIMIT
• Skip rows with OFFSET
• Build real pagination (page 1, 2, 3 …)
• Why ORDER BY matters with LIMIT
• TOP (SQL Server) and FETCH FIRST (ANSI)
• Why deep OFFSET is slow

Our Sample Table: articles

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

1. LIMIT — Just the First N Rows

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.

-- The table this lesson uses:
CREATE TABLE articles (
    id           INTEGER PRIMARY KEY,
    title        TEXT,
    score        INTEGER,
    published_on DATE
);
INSERT INTO articles VALUES
    (1, 'Postgres Performance', 980, '2026-01-14'),
    (2, 'Indexes Explained',    870, '2026-02-02'),
    (3, 'SQL Window Functions', 640, '2026-02-19'),
    (4, 'JSON in SQL',          520, '2026-03-05'),
    (5, 'Common Mistakes',      410, '2026-03-21'),
    (6, 'Getting Started',      300, '2026-04-08'),
    (7, 'Backups That Work',    260, '2026-04-27');

-- LIMIT caps how many rows come back
SELECT title, score
FROM articles
ORDER BY score DESC
LIMIT 3;

-- ✅ Expected result:
-- title | score
-- Postgres Performance | 980
-- Indexes Explained | 870
-- SQL Window Functions | 640

2. OFFSET — Skip Before You Take

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.

-- Uses the articles table from the first example.
-- OFFSET skips rows first, THEN LIMIT counts
SELECT title, score
FROM articles
ORDER BY score DESC
LIMIT 3 OFFSET 3;   -- skip top 3, return the next 3

-- ✅ Expected result:
-- title | score
-- JSON in SQL | 520
-- Common Mistakes | 410
-- Getting Started | 300

3. Real Pagination

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

-- Uses the articles table from the first example.
-- 📄 The pagination formula:  OFFSET = (page - 1) * page_size
-- Page size = 5, want page 3?  OFFSET = (3 - 1) * 5 = 10

SELECT title, published_on
FROM articles
ORDER BY published_on DESC
LIMIT 5 OFFSET 10;   -- rows 11–15

-- Change only the OFFSET to move between pages.

-- ✅ Expected result:
-- title | published_on

Your Turn: fetch page 2

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

-- Uses the articles table from the first example.
-- 🎯 YOUR TURN — fill in the two blanks, then "Try it Yourself"
-- Goal: return rows 6 through 10 of the cheapest-first list (page 2, 5 per page).

SELECT title, score
FROM articles
ORDER BY score ASC
LIMIT ___ OFFSET ___;   -- 👉 page size, then rows to skip

-- ✅ Expected: 5 rows — the 6th through 10th lowest scores.

4. TOP & FETCH — The Other Dialects

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.

-- Uses the articles table from the first example.
-- Same idea, three dialects. Pick the one your database speaks:

-- SQL Server (TOP goes after SELECT)
SELECT TOP 3 title, score FROM articles ORDER BY score DESC;

-- ANSI standard (Postgres, DB2, Oracle 12c+, SQL Server 2012+)
SELECT title, score FROM articles
ORDER BY score DESC
OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY;

-- MySQL / PostgreSQL / SQLite
SELECT title, score FROM articles ORDER BY score DESC LIMIT 3;

🧩 Reorder Challenge

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

-- Uses the articles table from the first example.
-- SCRAMBLED — arrange into a query that returns
-- "page 2" (5 per page) of articles, highest score first.

LIMIT 5 OFFSET 5
FROM articles
SELECT title, score
ORDER BY score DESC

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.

🧠 Quick Recall

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.

Common Errors (and the fix)

• LIMIT without ORDER BY: "the top 5" is undefined without a sort. The database may hand back any 5 rows, and a different 5 next time. Always pair LIMIT with ORDER BY.
• Wrong placement: SELECT title LIMIT 3 FROM articles is a syntax error — LIMIT goes at the end in MySQL/Postgres, while TOP goes right after SELECT in SQL Server.
• OFFSET off-by-one: OFFSET counts from 0. To skip the first 10 rows you write OFFSET 10, not OFFSET 11.
• Huge OFFSET on big tables: OFFSET 1000000 still scans a million rows first. For deep pages, prefer keyset pagination: WHERE id > :last_seen ORDER BY id LIMIT 20.

Mini-Challenge: Top 2 Newest

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

-- Uses the articles table from the first example.
-- 🎯 MINI-CHALLENGE — uses only this lesson's ideas
-- The homepage shows "Top 2 newest articles".
--   1. Order by published_on, newest first
--   2. Return only the first 2 rows
--
-- ✅ Expected: exactly 2 rows, the two most recent articles.

-- your query here

🎉 Lesson Complete

• ✅ LIMIT n caps the number of rows returned
• ✅ OFFSET m skips m rows first — the basis of pagination
• ✅ OFFSET = (page − 1) × page_size moves between pages
• ✅ Always pair LIMIT with ORDER BY for stable results
• ✅ TOP (SQL Server) and FETCH FIRST (ANSI) do the same job
• ✅ Next: give columns and tables friendly names with aliases

Practice quiz

Continue this course

• Previous: SELECT DISTINCT
• Next: Column & Table Aliases (AS) — Rename columns and tables in your queries for clarity
• Quick reference: SQL cheat sheet › Querying Data

Frequently asked questions

Is LIMIT standard SQL?

LIMIT is supported by PostgreSQL, MySQL, SQLite and many others, but it is NOT in the original SQL standard. The standard syntax is OFFSET ... FETCH FIRST ... ROWS ONLY, and SQL Server uses TOP. They all do the same job — capping how many rows return.

Should I always use ORDER BY with LIMIT?

Almost always, yes. Without ORDER BY the database can return any rows in any order, so 'the top 5' is meaningless and may change between runs. Pair LIMIT with ORDER BY to get a stable, predictable result.

How does LIMIT power pagination?

Page size goes in LIMIT and the page number drives OFFSET. For 10-per-page, page 1 is LIMIT 10 OFFSET 0, page 2 is LIMIT 10 OFFSET 10, page 3 is LIMIT 10 OFFSET 20, and so on: OFFSET = (page - 1) * page_size.

Why is large OFFSET slow?

OFFSET still makes the database read and discard every skipped row before returning your page. On page 10,000 that's a lot of wasted work. For deep pagination, 'keyset pagination' (WHERE id > last_seen_id) is far faster because it jumps straight to the rows.

What's the difference between TOP and LIMIT?

Functionally they cap row count the same way, but TOP goes right after SELECT (SELECT TOP 5 ...) in SQL Server, while LIMIT goes at the end of the query (... LIMIT 5) in MySQL/Postgres. TOP also supports PERCENT and WITH TIES.

Related lessons

• ORDER BY