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

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?

• Error in the question. Correct. IDs are unique. Commented Mar 3, 2019 at 15:30

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 ;
``````
• For those not familiar with the syntax used in this answer, refer to postgresql.org/docs/12/…. Commented Jul 27, 2019 at 17:52

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.