Batch pagination

example.sql

-- This is one technique for batching up the *first* page of results when doing pagination.
-- Potentially useful when using GraphQL and Dataloader.
-- 
-- In this example, we return the first 10 books whose author_id is 1,2,3,4 or 5 and genre
-- is "biography", ordered by "title"
--
-- For cursor-based (keyset) pagination, this technique won't work for anything after the
-- first "page", because the where clause needs to be the same for every entry in the
-- batch, which means you can't use different cursors. In practice, there isn't usually a
-- need to batch up subsequent pages, because your "next page" GQL queries will typically
-- only operate over one connection at a time.

SELECT
  * 
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY title) AS r,
    t.* as t
  FROM
    books t
  WHERE
    author_id in (1,2,3,4,5)
  AND
    genre = 'biography'
  ) x
WHERE
  x.r <= 10;

Nice, for keysets, you can use proper row-wise comparison too:

ROW(a,b) < ROW(c,d) is intepreted as a < c OR (a = c AND b < d)

Updated fiddle with 1000 rows and both approaches

https://www.db-fiddle.com/f/vjFjXod1UeznuFpP5jyQAY/2