SQL from zero · Chapter 5 of 37

One page at a time

By the end of this chapter you can sort rows in an order that never changes and serve them one page at a time, with OFFSET or with a cursor. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: First queries
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Meera's feed is live, and nobody wants 121 posts at once. She wants five at a time, and the order must never jump around while someone scrolls: no post shown twice, none skipped.

ORDER BY created_at DESC LIMIT 5 gives the first page, but you have no way yet to ask for the second. And when two rows tie on the sort key, Postgres may return them in either order, so a post at the edge of a page can appear on two pages, or on none.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    by-city.sql: active members sorted by city

    List every active member (no date in deleted_at), sorted by city A to Z, with the members who have no city at the very top. Members in the same city, and the members with no city, are sorted by username A to Z.

    Return two columns: username and city.

  2. Task 2

    most-liked.sql: the 5 most liked posts

    Find the 5 active posts (no date in deleted_at) with the most likes (likes_count), most first. Posts with the same likes: newest first (created_at). Posts with the same likes written at the same moment: highest id first.

    Return three columns: content, likes_count and created_at.

  3. Task 3

    page-two.sql: the second page of the feed

    The feed shows the active posts (no date in deleted_at) newest first (created_at), and posts written at the same moment highest id first, 5 posts a page. Return the second page: posts 6 to 10, in feed order.

    Return two columns: content and created_at.

  4. Task 4 · interview question

    Interview: next-page.sql, the next page after a cursor

    The members list shows every member, including those who have left the site, newest first (created_at), and members who joined at the same moment by username Z to A, 5 a page. The page on screen ended with the member who joined at 2026-03-01 00:00:00+05:30 with the username yash.

    Return the next 5 members after that row, in list order, with keyset pagination: a condition on created_at and username together. OFFSET is not allowed (the check looks for the word).

    Careful: another member may have joined at that exact moment with a username that comes before yash. They come next, so a condition on created_at alone would skip them.

    Return two columns: username and created_at.

What you will learn

  • Sorting by several keys: tie-breakers, a direction for each key, and where NULLs go.
  • Pages with OFFSET: skip the rows of the earlier pages.
  • Pages with a cursor: remember the last row shown and ask for what comes after it.

More in First queries

Build "One page at a time" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.