alphabet_pagination.sql

create table enrolments (
  id serial PRIMARY KEY,
  organisation_name text
)

-- Let's say we want to partition our table into pages of 50 rows
-- and sorted alphabetically.
with
dist as (
  select
    organisation_name,
    id,
    row_number() over (order by organisation_name, id) as row_number
  from enrolments
)
-- This will return the record at the 'top' of each page
-- the name+id serves as a natural key to use when fetching the page
-- We can construct our page links with this result
select row_number, organisation_name, id
from dist
where mod(row_number-1, 50) = 0
;

-- query to get the rows
select id, organisation_name
from enrolments
where organisation_name >= 'Acme Inc' and id >= 75
order by organisation_name, id
limit 50
;