How to get N rows after the first row matching a condition?

Question

I am implementing a cursor based pagination.

The requirement is to sort a dataset by an arbitrary condition (e.g. description) and return N rows after X row (where X represents the last item on the last page).

The first page is simple:

SELECT
  id
FROM probe
ORDER BY description ASC
LIMIT 5

Assuming this query returns IDs 4, 5, 2, 1, 3, then the next query must produce 5 next results from the same query after id 3.

Note: We cannot use LIMIT ... OFFSET after the first page because the offset is relative to the position of the referenced result that we do not know.

The only way I can think of solving this problem is:

Find row number in the dataset for ID 3:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  )
SELECT
  di1.row_number
FROM data_index di1
WHERE di1.id = 3;

Then use position to offset the dataset.

All together:

WITH
  data_index AS (
    SELECT
      d1.*,
      row_number() OVER () row_number
    FROM (
      SELECT
        id
      FROM probe
      ORDER BY description ASC
    ) d1
  ) 
SELECT
  di1.id
FROM data_index di1
WHERE di1.row_number > (
  SELECT
    di1.row_number
  FROM data_index di1
  WHERE di1.id = 3
)
LIMIT 10;

The downside of this approach is that data_index needs to load the entire sub-query into memory. Is there a more efficient way?

Answer 1

You could simply add the column used for the ordering (description) to the SELECT list and the WHERE clause of the following queries:

-- 1st query:
SELECT
  id, description
FROM probe
ORDER BY description, id
LIMIT 5 ;

Lets say that you get @id = 3 and @description = 'Beta' as the last (5th) row. You can then use these values to get the next (5) rows:

-- next query:
SELECT
  id, description
FROM probe
WHERE (description, id) > (@description, @id)
ORDER BY description, id
LIMIT 5 ;

Answer 2

If you order the result by description then you have to fetch the next portion by description not by id that is unrelated to the order.