Third Page of Users — LIMIT and OFFSET in SQL

The problem

Streamhub's user management dashboard paginates accounts alphabetically by name in pages of 10. An admin is navigating to page 3.

Write a query to return the ID, name, and email of the accounts on that page.

Assumptions:

The users table contains every account on the Streamhub platform.
Page 1 is rows 1–10, page 2 is rows 11–20, page 3 is rows 21–30.
To land on page 3, skip the first 20 rows with OFFSET 20, then take the next 10 with LIMIT 10.

Output:

One row per user on page 3, with columns id, name, and email, sorted by name ascending.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  name,
  email
FROM
  users
ORDER BY
  name
LIMIT
  10
OFFSET
  20

The shape

Page 3 of a 10-row pagination is rows 21 through 30, which means skipping the first 20 rows and taking the next 10 — OFFSET 20 LIMIT 10 on top of the alphabetical sort.

Clause by clause

SELECT id, name, email picks the three columns the dashboard shows per account. Other user fields stay out.
FROM users reads every Streamhub account. The pagination cut happens after the sort.
ORDER BY name sorts alphabetically — the same key the dashboard uses to define what page 3 means. Ascending is the default.
LIMIT 10 OFFSET 20 carves the page-3 window out of the sorted result. OFFSET 20 discards the first 20 names (pages 1 and 2 combined), and LIMIT 10 returns the next 10. Finn Kim at row 21 lands as the first row of page 3.

Why `OFFSET 20` and not `OFFSET 30`

The formula is OFFSET = (page - 1) * page_size. For a page size of 10:

Page 1: OFFSET 0 (no rows skipped, return rows 1–10)
Page 2: OFFSET 10 (skip first page, return rows 11–20)
Page 3: OFFSET 20 (skip first two pages, return rows 21–30)

The off-by-one is in the (page - 1). Page 3 doesn't skip 30 rows — that would land on page 4. It skips the rows that belong to the pages before page 3, which is 20.

The trap

The instinctive arithmetic — multiply the page number by the page size — overshoots by one page every time. OFFSET 30 LIMIT 10 returns rows 31–40, which is page 4. The fix is the - 1: subtract one from the page number before multiplying. A second trap to watch for: changing the page size also changes the offset. A page of 25 at page 3 is OFFSET 50, not OFFSET 20. The offset is anchored to the page size, not to the page number alone.

You practiced computing the OFFSET from a page number. The recurring formula is OFFSET = (page - 1) * page_size — once internalised, any page of any size becomes a one-line query.

Return the ID, name, and email of the accounts on that page

The shape

Clause by clause

Why `OFFSET 20` and not `OFFSET 30`

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why OFFSET 20 and not OFFSET 30

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why `OFFSET 20` and not `OFFSET 30`