SQL ORDER BY and LIMIT: Top-N, OFFSET and Keyset Pagination

ORDER BY and LIMIT look like the easiest clauses in SQL: sort, then keep a few. Their bugs hide in development. A page that repeats a row, a leaderboard that reshuffles on refresh, a list that slows with every page: each comes from a missing sort, a sort with ties, or an OFFSET that reads everything it skips. Every query below was run with Python's sqlite3 module (SQLite 3.37).

The problem it solves

Two everyday questions:

• Top N: the three fastest runners, the ten newest posts, the five largest orders. One sorted slice, one round trip.
• Pagination: page after page of the same sorted list, where page k must continue exactly where page k - 1 stopped: no gaps, no repeats.

Both rest on one fact: a SQL result has no order unless ORDER BY gives it one. Without it, rows come back in whatever order the access path produces, which can change when an index is added or the table grows.

The intuition

Sort, then cut. ORDER BY defines the sequence and LIMIT takes a prefix of it. Logically the sort runs after WHERE, grouping and SELECT, and the cut comes last, as the order of execution shows.

Make the order total. Rows that tie on the sort key have no defined relative order, which may differ between two runs of the same query. For pagination that means a row can appear on two pages or on none. Add a unique column as the last sort key: ORDER BY seconds, bib.

The engine does not have to sort everything. With a LIMIT, it can keep only the best N rows while it reads; PostgreSQL's plans call this a top-N heapsort. With an index already in sort order, it walks the index and stops after N rows.

OFFSET skips by reading. LIMIT 10 OFFSET 9000 still produces rows 1 to 9,000 and throws them away.

Keyset pagination remembers where you stopped. Ask for "rows after the last one I showed": WHERE id > :last_id ORDER BY id LIMIT 10. With an index on the sort key, that is a seek plus ten rows on any page. The cursor is the last row's full sort key, tie-breaker included.

Watch it run

The animation uses the lesson's runner table. With no ORDER BY, rows arrive in whatever order the engine found convenient, with no promise, so LIMIT 3 alone just cuts three arbitrary rows off an unordered pile. ORDER BY seconds ASC gives the pile a meaning, so the engine sorts it. Bruno's 8755 is the smallest elapsed time, so Bruno goes first; then Dara at 8890 and Chi at 9040, and Ada's 9120 lands last. Now LIMIT 3 means something: take the first three and stop reading. The podium board comes back in a single round trip, Bruno, Dara, Chi, and nobody printed the full field. Because of the LIMIT, the engine can often stop early instead of sorting the whole field. Page two is LIMIT 3 OFFSET 3: skip the podium and take the next slice of the same sort.

The code

The lesson's table plus Eun, who ties with Dara; the unique bib breaks the tie. The plan shows the sort, then shows it vanish once an index matches the ORDER BY:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE runner (bib INTEGER PRIMARY KEY, name TEXT, seconds INTEGER);
  INSERT INTO runner VALUES (14, 'Ada', 9120), (7, 'Bruno', 8755),
                            (22, 'Chi', 9040), (3, 'Dara', 8890), (9, 'Eun', 8890);
""")
podium = "SELECT name, seconds FROM runner ORDER BY seconds, bib LIMIT 3"
print(db.execute(podium).fetchall())
# [('Bruno', 8755), ('Dara', 8890), ('Eun', 8890)]

plan = lambda sql: [row[3] for row in db.execute("EXPLAIN QUERY PLAN " + sql)]
print(plan(podium))
# ['SCAN runner', 'USE TEMP B-TREE FOR ORDER BY']
db.execute("CREATE INDEX by_time ON runner(seconds, bib)")
print(plan(podium))
# ['SCAN runner USING INDEX by_time']

How much does OFFSET read? A function in the WHERE clause counts every row evaluated, on 10,000 posts. Both queries return the same page:

big = sqlite3.connect(":memory:")
big.execute("CREATE TABLE post (id INTEGER PRIMARY KEY, title TEXT)")
big.executemany("INSERT INTO post VALUES (?, ?)", ((i, "post %d" % i) for i in range(1, 10_001)))
touched = 0
def touch(_):
    global touched
    touched += 1
    return 1
big.create_function("touch", 1, touch)

def rows_read(sql, *args):
    global touched
    touched = 0
    page = big.execute(sql, args).fetchall()
    return [r[0] for r in page][:3], touched

print(rows_read("SELECT id FROM post WHERE touch(id) ORDER BY id LIMIT 10 OFFSET 9000"))
# ([9001, 9002, 9003], 9010)
print(rows_read("SELECT id FROM post WHERE id > ? AND touch(id) ORDER BY id LIMIT 10", 9000))
# ([9001, 9002, 9003], 10)

Speed is not the only difference. Between two clicks, someone publishes post 9. The OFFSET query for page two shifts by one and shows post 6 again; the keyset query continues after the last id the reader saw:

feed = sqlite3.connect(":memory:")
feed.execute("CREATE TABLE post (id INTEGER PRIMARY KEY)")
feed.executemany("INSERT INTO post VALUES (?)", [(i,) for i in range(1, 9)])
newest = "SELECT id FROM post ORDER BY id DESC LIMIT 3"
page1 = [r[0] for r in feed.execute(newest)]
feed.execute("INSERT INTO post VALUES (9)")          # someone posts between clicks
by_offset = [r[0] for r in feed.execute(newest + " OFFSET 3")]
by_key = [r[0] for r in feed.execute(
    "SELECT id FROM post WHERE id < ? ORDER BY id DESC LIMIT 3", (page1[-1],))]
print(page1, by_offset, by_key)
# [8, 7, 6] [6, 5, 4] [5, 4, 3]

Checked on 300 seeded random tables with heavily tied times and random page sizes: keyset pages, using a row-value cursor on (seconds, bib), must concatenate to exactly the brute-force answer, every row sorted in Python, and must equal the OFFSET pages on the unchanged table:

import random

def keyset_pages(con, size):
    pages, last = [], None
    while True:
        if last is None:
            rows = con.execute("SELECT seconds, bib FROM r ORDER BY seconds, bib LIMIT ?", (size,)).fetchall()
        else:
            rows = con.execute("SELECT seconds, bib FROM r WHERE (seconds, bib) > (?, ?) "
                               "ORDER BY seconds, bib LIMIT ?", (*last, size)).fetchall()
        if not rows:
            return pages
        pages.append(rows)
        last = rows[-1]                  # the cursor: the last row's full sort key

def offset_pages(con, size):
    pages, offset = [], 0
    while True:
        rows = con.execute("SELECT seconds, bib FROM r ORDER BY seconds, bib LIMIT ? OFFSET ?",
                           (size, offset)).fetchall()
        if not rows:
            return pages
        pages.append(rows)
        offset += size

random.seed(28)
ok = True
for _ in range(300):
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE r (bib INTEGER PRIMARY KEY, seconds INTEGER)")
    bibs = random.sample(range(1, 1000), random.randint(0, 60))
    rows = [(b, random.randint(1, 8)) for b in bibs]      # many tied times
    con.executemany("INSERT INTO r VALUES (?, ?)", rows)
    size = random.randint(1, 7)
    expected = sorted((s, b) for b, s in rows)             # brute force: sort in Python
    keyset = keyset_pages(con, size)
    ok &= [row for page in keyset for row in page] == expected
    ok &= keyset == offset_pages(con, size)
    ok &= all(len(p) == size for p in keyset[:-1])
print(ok)                                  # True

The complexity

For a table of n rows, a page of k and an offset of m:

• Top-N without a useful index: read all n rows, keep the best k: about O(n log k).
• Top-N with an index in sort order: O(log n + k), a seek and k rows.
• OFFSET m pagination: O(log n + m + k) even with the index, because the skipped rows are still produced.
• Keyset pagination: O(log n + k) on every page, given an index on the full sort key. The SQL cheat sheet puts LIMIT and OFFSET last in the evaluation order: they cut rows already produced.

Where it goes wrong

• LIMIT without ORDER BY. Arbitrary rows, stable only by accident.
• Sorting on a non-unique column. Ties reorder between queries and pages repeat or skip rows. Add a unique tie-breaker.
• Keyset cursor on the sort column alone. With ties, WHERE seconds > :last skips the rest of a tied group. Compare the full key, as the row value above does, or spell it out with OR.
• Deep OFFSET pages. Fine for page 3, slow for page 3,000. Keyset gives up "jump to page 400", usually a fair trade.
• NULLs in the sort key. Engines disagree on where NULLs sort: SQLite and MySQL put them first in ascending order, PostgreSQL last. Say NULLS FIRST or NULLS LAST where supported.
• Assuming LIMIT is universal. As of September 2026, the standard form is FETCH FIRST n ROWS ONLY, and SQL Server also uses TOP or OFFSET ... FETCH.

When it shows up in interviews

In SQL rounds as "top N" questions, which become "top N per group" once a category is added; that needs window functions. In system design, every feed and list API needs pagination, and cursor-based pagination is the expected answer for the news feed. An index on (seconds, bib), as in composite indexes, is what makes the top-N and the keyset seek cheap.

How to say it in an interview

"LIMIT only means something after an ORDER BY, and the order has to be total, so I add a unique tie-breaker. With an index matching the sort, the engine reads the first index entries and stops. For pagination I avoid deep OFFSETs: the database still produces every skipped row, and rows shift when new data arrives. I use keyset pagination: the client sends back the last row's sort key, and the next page is WHERE the key is greater than that cursor, ORDER BY the key, LIMIT the page size. That is a seek plus one page, however deep."