Module 1: Foundations of SQL and the Relational Model ⏱ 16 min

LIMIT and OFFSET

By the end of this lesson you will be able to:
  • Restrict result sets to a specific number of rows using LIMIT
  • Skip rows at the start of a result set with OFFSET
  • Combine LIMIT and OFFSET to paginate through sorted results

Why limit at all?

A database can hold millions of rows, but a screen shows twenty. Returning every row when you only need three is wasteful — it ships data nobody reads and slows the query to a crawl. LIMIT caps how many rows come back; OFFSET skips rows before that cap begins. Together they answer two of the most common questions in software: "give me the top three" and "give me the next page."

Both clauses sit at the very end of a query, after ORDER BY, because they only make sense once the rows are chosen, filtered, and sorted. Everything else decides which rows qualify; LIMIT and OFFSET decide how many of the survivors to actually hand back.

The three highest salaries — LIMIT trims the sorted result to three rows.
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

The two clauses

LIMIT n returns at most n rows — if fewer rows qualify, you get fewer, never padding. OFFSET m discards the first m rows of the sorted result, then LIMIT applies to whatever remains. Write them as a pair: LIMIT 5 OFFSET 10 means skip ten, then keep five.

Offset starts at zero, not one. OFFSET 0 skips nothing, so the first page is LIMIT 5 OFFSET 0 — many people just write LIMIT 5, since an omitted OFFSET defaults to zero. Asking for an OFFSET larger than the row count simply returns nothing, which is the clean signal that you have paginated past the final page.

graph TD
  A["All rows sorted"] --> B["OFFSET 3<br/>skip first 3"]
  B --> C["LIMIT 2<br/>keep next 2"]
  C --> D["Final result:<br/>rows 4 and 5"]
  style B fill:#0e7490,color:#fff
  style C fill:#0e7490,color:#fff
OFFSET skips rows at the top; LIMIT trims everything after the desired page.

Paginating, one page at a time

Pick a fixed page size — say five rows — and the pages fall out as a simple pattern. Page one is LIMIT 5 OFFSET 0, page two is LIMIT 5 OFFSET 5, page three is LIMIT 5 OFFSET 10. The OFFSET is always page_size times (page_number minus one).

This is exactly how search results, product listings, and feeds are built: each click of "next" runs the same query with a larger OFFSET. The sort order must stay identical across pages, or rows wander between pages — appearing on page one and again on page three, or vanishing entirely. A stable ORDER BY is what makes pagination trustworthy.

Page two of an alphabetical staff list: skip five, keep five.
SELECT name, salary
FROM employees
ORDER BY name
LIMIT 5 OFFSET 5;
flowchart LR
  P1["Page 1<br/>LIMIT 5 OFFSET 0<br/>rows 1-5"] --> P2["Page 2<br/>LIMIT 5 OFFSET 5<br/>rows 6-10"] --> P3["Page 3<br/>LIMIT 5 OFFSET 10<br/>rows 11-15"]
  style P1 fill:#0e7490,color:#fff
  style P3 fill:#0e7490,color:#fff
Pagination is the same query repeated with a growing OFFSET, each page a fixed-size window.

The determinism rule

Rows leave a table in whatever order the engine finds convenient, which is not guaranteed to be insertion order or any order at all. Add LIMIT 5 with no ORDER BY and you may receive five arbitrary rows — different ones after a backup, a reindex, or even a new database version. The result looks fine in testing and then breaks in production.

The fix is a rule with no exceptions: whenever you write LIMIT, sort first. ORDER BY turns an arbitrary stream into a deterministic sequence, so the same query returns the same rows on every run. OFFSET without ORDER BY is even worse, because the set of skipped rows is undefined too.

LIMIT sees only the final rows

Because LIMIT and OFFSET run last, they apply to whatever the rest of the query produced — after joins, filters, and grouping. A query that joins employees to departments, filters to active projects, and then asks for LIMIT 5 returns five rows of that joined, filtered result, not five employees or five departments.

This composability is what makes LIMIT safe to bolt onto almost any query: it never changes which rows qualify, only how many you ultimately receive. Build the full result first with the clauses you already know, then trim it at the end.

When rows tie at the boundary

Top-N queries hide a quiet problem: ties. If you ask for the three highest salaries and two employees share the third-place salary, LIMIT 3 picks one of them arbitrarily — the choice is not defined, and it can change between runs.

The query is not wrong; the data simply does not contain enough information to break the tie. For a leaderboard that must stay stable, add a tiebreaker column to the ORDER BY. ORDER BY salary DESC, name ASC makes equal salaries fall back to alphabetical order, so the result no longer depends on chance.

Exercise

Return the name and salary of the 2 lowest-paid employees.

SELECT name, salary
FROM employees
-- sort and limit here
;
Exercise

Return the name and hired_on date of the most recently hired employee. Only return 1 row.

SELECT name, hired_on
FROM employees
-- sort and limit here
;
Exercise

Return the name and salary of employees sorted by salary highest first, skipping the top 3 and returning the next 2.

SELECT name, salary
FROM employees
-- sort, offset, and limit here
;
Exercise

Show page 2 of an alphabetical staff list, four employees per page. Return each employee's name and salary, sorted by name, skipping the first page.

SELECT name, salary
FROM employees
-- sort, then paginate to page 2 here
;
Exercise

This query means to skip the top 3 salaries and keep the next 2, but OFFSET appears before LIMIT — SQLite refuses to run it. Put the two clauses in the correct order.

SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 3 LIMIT 2;

Recap

  • LIMIT n returns at most n rows; OFFSET m skips the first m rows first.
  • Both clauses go at the very end, immediately after ORDER BY.
  • Pagination is a fixed page size with a growing OFFSET: page size times (page minus one).
  • Always sort before you limit — without ORDER BY the chosen rows are arbitrary.
  • LIMIT trims the final result, so it composes safely with joins, filters, and grouping.

Next you will combine everything you have learned into a single multi-step company report.

Checkpoint quiz

Which clause must come immediately before LIMIT in a standard query?

What is the effect of LIMIT 5 OFFSET 10?

Why can LIMIT 3 without ORDER BY return different rows on different days?

Go deeper — technical resources