SQL LIMIT and OFFSET let an application retrieve a small, ordered window from a larger query result. LIMIT sets the maximum number of returned rows; OFFSET skips a specified number of preceding rows. This super-fundamental lesson follows OSSQL.004: ORDER BY and demonstrates page-based results using a fictional Bitcoin-mining equipment table.

Learning objectives

• Define LIMIT, OFFSET, and pagination using precise terminology.
• Return the first, second and third page of a sorted dataset.
• Calculate offsets with a page number and page size.
• Explain why nonunique ordering and changing data can alter pages.
• Recognize the cost of very large offsets and when to research keyset pagination.

Earlier lessons introduced relational tables, SELECT and FROM, and WHERE conditions. Those operations identify rows. Pagination determines which portion of those selected rows the user sees. The official PostgreSQL 18 reference requires a predictable ORDER BY when a consistent subset matters.

Alex The Analyst demonstrates ORDER BY, LIMIT and OFFSET using PostgreSQL. Source: instructor’s original July 2026 lesson.

LIMIT: Return no more than N rows

A basic query uses SELECT ... FROM ... ORDER BY ... LIMIT 3. It returns at most three rows from the sorted result. If only two rows exist, it returns two, not an error. A missing ORDER BY is not equivalent to “sort by insertion order”: SQL does not promise a natural row order.

SELECT miner_id, model, hashrate_th
FROM miners
ORDER BY hashrate_th DESC, miner_id ASC
LIMIT 3;

The example sorts by the highest hypothetical terahashes-per-second first, breaking equal-hashrate ties with the unique miner_id. That final key makes the sorting total for the unchanged table. DESC and ASC were introduced in the preceding lesson.

OFFSET: Skip rows before returning a page

OFFSET 3 discards the first three ordered rows before returning up to the requested limit. Unlike page numbering in a user interface, the offset is zero-based: the first row is reached with OFFSET 0. Use the form LIMIT n OFFSET m rather than SQLite’s confusing comma-style shorthand, where the positions reverse.

Thapa Technical explains sorting and page-based query results with PostgreSQL LIMIT and OFFSET in a June 2026 tutorial.

Reproducible training lab: eight fictional miners

The following dataset is entirely fabricated for education; the labels are not commercial models and the hashrate values are not manufacturer claims. Copy the complete CREATE TABLE and INSERT statements into a new local SQLite database, then run the page queries. The script uses only elementary SQLite-compatible syntax.

-- Fictional training data, NOT manufacturer specifications
CREATE TABLE miners (
    miner_id INTEGER PRIMARY KEY,
    model TEXT NOT NULL,
    hashrate_th INTEGER NOT NULL
);

INSERT INTO miners (miner_id, model, hashrate_th) VALUES
(101, 'Training A', 140),
(102, 'Training B', 188),
(103, 'Training C', 220),
(104, 'Training D', 100),
(105, 'Training E', 188),
(106, 'Training F', 270),
(107, 'Training G', 140),
(108, 'Training H', 335);

For a page size of three, use the same two-column sort in every query. A unique final key avoids ambiguity when two records have the same hashrate.

SELECT miner_id, model, hashrate_th
FROM miners
ORDER BY hashrate_th DESC, miner_id ASC
LIMIT 3 OFFSET 0;

SELECT miner_id, model, hashrate_th
FROM miners
ORDER BY hashrate_th DESC, miner_id ASC
LIMIT 3 OFFSET 3;

SELECT miner_id, model, hashrate_th
FROM miners
ORDER BY hashrate_th DESC, miner_id ASC
LIMIT 3 OFFSET 6;

I executed this exact training dataset with Python’s SQLite 3.46.1 engine. The observed ordered IDs were:

Page 1: 108, 106, 103
Page 2: 102, 105, 101
Page 3: 107, 104
Page 4: (no rows)

The third page contains only two records because only eight records exist. A hypothetical fourth page yields no rows. This is ordinary pagination behavior, not an error.

Page-number math

Applications often show page 1, page 2 and page 3 while SQL uses zero-based offsets. For positive page number p and page size n, calculate OFFSET = (p − 1) × n. When n = 3, the offsets are 0, 3 and 6. In production software, validate the supplied values and bind them as parameters rather than concatenate untrusted input into SQL text.

page_size = 3
page = 2
offset = (page - 1) * page_size
# offset = 3
# SQL: LIMIT 3 OFFSET 3

A server might compute the offset inside a typed PHP function or a Python request handler. The database receives a prepared statement with values for the page limit and offset. That separation prevents request parameters from being interpreted as SQL commands.

iNeuron Intelligence’s SQL tutorial explains result limiting, skipped rows and their use in application pagination (January 2024).

Troubleshooting changing pages

Suppose a new high-hashrate row is inserted between fetching page 1 and page 2. Even with a fully deterministic ORDER BY, the row positions have now shifted, so one previously seen row might reappear or another might be missed. Stable ordering solves ties within a fixed dataset; it does not freeze the underlying table. For consistent historical reporting, use a suitable snapshot strategy. For large sequential feeds, keyset pagination may be preferable.

SQLite’s row-value documentation explains that deep offsets can require work proportional to skipped rows. PostgreSQL also warns that skipped rows must be computed. LIMIT 20 OFFSET 500000 does not guarantee an inexpensive database seek. Indexes and query plans still matter, much as they do in memory-sensitive infrastructure.

Knowledge check

1. What does LIMIT 3 guarantee, and what does it not guarantee?
2. Why does OFFSET 3 return the fourth ordered row first?
3. For page 5 with a page size of 10, what offset should you use?
4. Why include miner_id after hashrate_th in ORDER BY?
5. Does a total sort order prevent changing pages when new rows are inserted?
6. Why might a query with a very large offset become slow?

Answers

1. It caps output at three rows but does not determine which rows without sorting. 2. It skips the first three rows. 3. OFFSET 40. 4. It resolves ties with a unique key. 5. No: inserts or deletes between requests can shift positions. 6. The database still processes the preceding rows, and a different pagination method may be more efficient.

Primary references and next lesson

Read PostgreSQL 18: LIMIT and OFFSET, SQLite SELECT/LIMIT, SQLite scrolling-window examples, and Microsoft Learn’s PostgreSQL pagination module. The next lesson in the full curriculum rotation is Bash; the next SQL-specific lesson will be selected after comparing SQL sequence and existing published material.

Editor’s Note: We volunteer daily to ensure the credibility of the information on this platform is Verifiably True. If you would like to support our work and help further secure the integrity of our research initiatives, please scan the QR code below to donate 5,000 sats.

For mobile scanning, open this lesson on a larger screen and use a compatible Bitcoin wallet to scan the original QR code. Verify the address before sending 5,000 sats: 3C9o19EH5HSiwEPyCTmEKzxhNCbo2X6TTb.

BitcoinVersus.tech is not a financial advisor. This media platform reports on financial subjects purely for informational purposes.