Paging: page 3 of the run-scorers

Every list on every website is paged: rows 1-10, then 11-20. Three ways to fetch "page n", and one that scales when the list is long.

The test case

The 2026 run-scorers, most runs first (ties alphabetical), ten per page. Show page 3.

Expected result

Page 3 is where the openers who got starts and the finishers who didn't get the strike live.

Way 1: OFFSET ... FETCH NEXT

select p.player_name, sum(b.runs_batter) as runs
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
where  b.season = 2026
group  by p.player_name
order  by runs desc, p.player_name
offset 20 rows fetch next 10 rows only;

Skip (page - 1) × 10 rows, take 10. In an app: offset :skip rows fetch next :page_size rows only.

Way 2: ROW_NUMBER

select player_name, runs
from   (select p.player_name, sum(b.runs_batter) as runs,
               row_number() over (order by sum(b.runs_batter) desc, p.player_name) as rn
        from   cr_balls_v b
        join   cr_players p on p.player_id = b.batter_id
        where  b.season = 2026
        group  by p.player_name)
where  rn between 21 and 30
order  by rn;

This is roughly what Oracle turns Way 1 into behind the scenes.

Way 3: ROWNUM, three levels deep

select player_name, runs
from   (select x.*, rownum as rn
        from   (select p.player_name, sum(b.runs_batter) as runs
                from   cr_balls_v b
                join   cr_players p on p.player_id = b.batter_id
                where  b.season = 2026
                group  by p.player_name
                order  by runs desc, p.player_name) x
        where  rownum <= 30)
where  rn > 20
order  by rn;

Sort inside, number and stop at 30 in the middle, throw away the first 20 outside. You'll find this shape in every Oracle application written before 2013.

Way 4: keyset paging ("seek")

The three ways above all read and sort rows 1 to 20 just to throw them away. Page 500 reads 5,000 rows. Keyset paging remembers where the last page ended and starts from there. Page 2 ended with Thabo Mokoena on 103 runs:

select p.player_name, sum(b.runs_batter) as runs
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
where  b.season = 2026
group  by p.player_name
having sum(b.runs_batter) < 103
or     (sum(b.runs_batter) = 103 and p.player_name > 'Thabo Mokoena')
order  by runs desc, p.player_name
fetch  first 10 rows only;

"Fewer runs than the last row, or the same runs and later in the alphabet." With an index on the sort columns (on a real table, not an aggregate like this), the database jumps straight to the right spot, so page 500 costs the same as page 1. The price: no "jump to page 37" button, only next and previous.

19c and 26ai

The same on both.

Gotchas

• The ORDER BY must be unique, or rows can appear on two pages or none as the sort shuffles ties. Here player_name breaks the ties.
• Data changes between page requests. With OFFSET, a new row on page 1 pushes a row from page 2 to page 3, and the reader sees it twice. Keyset paging doesn't have that problem.