In keyset pagination, how do you query the previous page?
answer
- you want the nearest rows, not the farthest
- anchor on the first row of the current page
- flip the comparison and the ORDER BY together
- re-sort the block in an outer query
basics
~20 sAnchor on the first row of the current page, flip both the comparison and the ORDER BY, and take the same LIMIT, then reverse those rows in an outer query to restore display order. Flipping only one of the two returns the wrong rows.
solid answer
~50 sForward paging with `(created_at, id) < (:anchor_ts, :anchor_id) ORDER BY created_at DESC, id DESC LIMIT 20` takes the 20 rows *nearest* the anchor going down. To go back you need the 20 nearest going up, so you anchor on the **first** row of the current page and flip both parts: `(created_at, id) > (:first_ts, :first_id) ORDER BY created_at ASC, id ASC LIMIT 20`. Flipping the comparison but leaving `ORDER BY ... DESC` is the classic mistake — `LIMIT` would then take the 20 rows furthest from the anchor, meaning the newest rows in the table rather than the page just above. Because the inner query returns rows ascending, wrap it in a derived table and re-sort `DESC` so the page displays in the same order as every other page. Fewer than 20 rows means you have reached the start.
code
sql · 17 lines-- Forward: 20 rows after the LAST row of the current page
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Backward: 20 rows before the FIRST row of the current page,
-- re-sorted into display order by the outer query
SELECT * FROM (
SELECT id, created_at, title
FROM posts
WHERE (created_at, id) > (:first_created_at, :first_id)
ORDER BY created_at ASC, id ASC
LIMIT 20
) AS prev_page
ORDER BY created_at DESC, id DESC;go deeper
Know that going back means comparing in the opposite direction against the first row of the current page, and that the rows come out reversed and need re-sorting.
Explain why both the operator and the ORDER BY must flip: LIMIT takes rows in sort sequence, so leaving the ORDER BY alone returns the far end of the table instead of the adjacent page.
Show you have shipped it: two cursors in the API, an extra row fetched to signal has-more, end-of-data detection in both directions, and tests over duplicate sort values and short pages.
Decide the navigation contract up front. Prev/next cursors and arbitrary page jumps are mutually exclusive at scale; pick one, apply it across every listing, and make sure the query-building layer derives operator and ordering from a single definition so they cannot drift apart.
## Why you cannot just flip the operator The seek predicate selects a half-open region of the sort order, and `LIMIT` then takes rows *in `ORDER BY` sequence* from that region. Forward paging works because the region begins at the anchor and the ordering starts there too, so the first 20 rows are the 20 immediately after it. Going backwards, the region `(created_at, id) > (:first_ts, :first_id)` contains everything above the anchor — potentially the entire table. If you keep `ORDER BY created_at DESC, id DESC`, the sequence starts at the *newest* row in the table, so `LIMIT 20` hands you the first page of the feed, not the page immediately above where the user is. The rows are all inside the region, and all wrong. The fix is to reverse the sequence so it starts at the anchor again. Both halves flip together: the comparison operator and every direction in the `ORDER BY`. ## The query ```sql SELECT * FROM ( SELECT id, created_at, title FROM posts WHERE (created_at, id) > (:first_created_at, :first_id) ORDER BY created_at ASC, id ASC LIMIT 20 ) AS prev_page ORDER BY created_at DESC, id DESC; ``` The inner query walks upward from the anchor and stops after 20 rows — the 20 immediately above the current page, in reverse display order. The outer query re-sorts those 20 rows back into the feed's normal `DESC` order. It is a sort of 20 rows, so it costs nothing. Note the anchor: the **first** row of the page currently on screen, not the last. Forward paging anchors on the last row; backward paging anchors on the first. A UI doing both therefore carries two cursors, conventionally exposed as `next` and `prev` tokens. ## Detecting the ends - **Start of the data.** The backward query returns fewer than the page size — that block is the beginning, and there is no previous page beyond it. - **End of the data.** The forward query returns fewer than the page size. A common refinement is to request `LIMIT 21` for a page of 20: if 21 rows come back there is another page, and you discard the extra. That gives an accurate "has more" flag without a second query and without a count. ## What backward paging still cannot do It gives you the page immediately above, one step at a time. It cannot express "three pages back" or "page 12" — jumping requires counting rows, which is the whole thing keyset pagination removed. If the UI needs arbitrary jumps, either keep `OFFSET` for shallow depths and accept its cost and drift, or change the UI to prev/next plus "back to top". It also cannot mix orderings. The backward query must use the same columns, the same filters and the mirrored directions as the forward one; anything else makes the anchor meaningless and quietly returns the wrong rows. ## Two ways to keep it honest The flip-both rule is easy to state and easy to get wrong in code, so it is worth centralising. Two approaches work well: 1. **Build the statement from a direction flag.** One query builder takes the sort columns, a direction, and an anchor, and emits the operator and the `ORDER BY` from the same source of truth. The two can then never disagree. 2. **Reverse the sort in one place.** Define the ordering once as a list of column-and-direction pairs; the backward case maps over it inverting each direction and picks the mirrored operator. The outer re-sort uses the original list unchanged. Either way, test it against a fixture with duplicate sort values and with fewer rows than a full page in both directions — the boundaries are where the bugs live. A useful invariant for the test: paging forward n times and then backward n times must return exactly the rows you started on.
- Why is the outer ORDER BY needed at all?The inner query must sort ascending so that LIMIT takes the 20 rows nearest the anchor rather than the 20 furthest, and those rows therefore arrive in reverse display order. The outer sort of 20 rows puts them back into the feed's normal order at negligible cost; the alternative is reversing them in application code, which works but hides the ordering contract from the query.
- How do you tell the client whether a next or previous page exists?Request one row more than the page size — LIMIT 21 for a page of 20. If 21 come back, there is another page in that direction; drop the extra row before rendering. It costs one row instead of a separate count query, and it is accurate at the moment the page was read.
- Can you jump three pages back with keyset pagination?Not directly. Keyset expresses "the rows adjacent to this value", and arbitrary jumps require counting rows — exactly what OFFSET does and what keyset removes. The options are stepping back three times, keeping the anchors of recently visited pages client-side, or accepting OFFSET for shallow jumps.
saying these in an interview costs you the question
- Flips the comparison but leaves the ORDER BY unchanged
- Anchors the previous page on the last row instead of the first
- Forgets to re-sort the backward page into display order
- Thinks previous-page support means you can jump to any page
- Uses a different tie-breaker or filter set for the backward query