Part 4 of 5 · 2 min read
Window functions for paging
A window function can compute a row's position directly, useful when a row's rank itself is part of what is needed.
Window functions for paging
Think of a numbered ticket system at a counter. Each ticket's number tells a customer exactly their position in line, computed once as tickets are issued, rather than recounted from scratch for every inquiry.
In plain words: a window function such as ROW_NUMBER() can compute each row's position within an ordered result directly, as an extra computed column, without changing how many rows are physically read. This is useful specifically when the row's actual rank or position needs to be known or displayed, not just used to decide what to skip, something plain OFFSET or keyset pagination alone do not provide. It does not, by itself, solve the growing cost of OFFSET; it is a tool for a related but distinct need.
A worked example. SELECT *, ROW_NUMBER() OVER (ORDER BY placed_on) AS position FROM orders; labels every row with its position in the overall sorted order, useful for displaying "order 1,042 of 50,000" alongside a result.
The key lines.
SELECT *, ROW_NUMBER() OVER (ORDER BY placed_on) AS position FROM orders;
-- computes each row's rank in the sorted order as an extra column
Window function (for paging)
A window function, such as ROW_NUMBER(), computes a value, like a row's rank, over an ordered set of rows without collapsing them into groups, useful when a row's position itself needs to be known or shown.
Example: a numbered ticket that tells a customer their exact position in line, computed once as tickets are issued, mirrors a window function's row-position computation.
Remember this
OFFSET is simple but gets slower on every later page; keyset pagination stays fast at any depth by filtering on a remembered key instead of counting rows. A window function solves a different problem: knowing a row's exact rank, not just skipping ahead.
Quick notes
OFFSETmust generate and discard every skipped row, growing more expensive on later pages.- Keyset pagination remembers the last row's key and filters for rows after it, staying roughly constant in cost.
- A sargable filter and a supporting index are what make keyset pagination fast.
- A window function like
ROW_NUMBER()computes a row's position directly, useful for display, not for making deep pages cheap. - Keyset pagination is generally the better default for deep, high-traffic pagination.