Lesson 15 of 61

5 mins read

SQL SELECT TOP, LIMIT & OFFSET Pagination

In Plain English

The `LIMIT` and `OFFSET` clauses restrict the number of rows returned by a query and skip a designated number of initial rows, enabling database pagination. Different database dialects use `LIMIT` (PostgreSQL, MySQL, SQLite), `SELECT TOP` (SQL Server), or ANSI standard `FETCH FIRST n ROWS ONLY`.

The Mental Model

"`LIMIT 10 OFFSET 20` is like reading page 3 of a book where every page has 10 lines: skip the first 20 lines (OFFSET 20) and read the next 10 lines (LIMIT 10)."

Deep Dive: How It Works

LIMIT and OFFSET Syntax: `SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20` (Page 3 with page size 10).

Mandatory ORDER BY: Pagination without an `ORDER BY` clause is non-deterministic: the database may return duplicate or omitted rows across pagination calls.

Deep Paging Inefficiencies: `OFFSET 1000000 LIMIT 10` forces the database to read and discard 1 million rows; keyset pagination (`WHERE id > last_seen_id LIMIT 10`) is far superior.

Syntax Blueprint

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC
LIMIT 5 OFFSET 10;   -- Skip 10 rows, take next 5

Always combine LIMIT and OFFSET with an unambiguous ORDER BY clause.

Core Rules to Remember

Deterministic Pagination: Always sort on unique keys (e.g. primary key) to guarantee stable page views.

LIMIT with ORDER BY DESC: `ORDER BY score DESC LIMIT 3` is the standard pattern for top-N ranking queries.

Dialect Differences: Postgres/SQLite/MySQL use LIMIT/OFFSET; SQL Server uses TOP / OFFSET FETCH.

Common Beginner Traps & How to Fix Them

Mistake:Using `LIMIT 1` without `ORDER BY` and expecting the "first" inserted row.

Why it happens: RDBMS tables have no intrinsic order.

How to fix: Include `ORDER BY id ASC LIMIT 1`.

Pro Tip: For massive tables, replace high OFFSET values with keyset pagination: `WHERE id > :last_id ORDER BY id ASC LIMIT 20`.

Live Interactive Example

Hit Run Code to see it live

Top-3 Ranking and Page Pagination with LIMIT/OFFSET

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: LIMIT 2 returned top 2, and OFFSET 2 skipped the top 2 to fetch page 2.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Query Top 2 Most Expensive Products

Select `name` and `price` from `hardware` ordered by `price DESC LIMIT 2`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.