How can I use pagination using GridDB for the API

Question

How can I implement the pagination in GridDB api like fetching a smaller chunk of data from the database at a time and allow us to fetch the next chunk by defining the page size (number of records per query) and the page number.

I have the following Pet table.

id petname category age
1 AMOR Dog Young
2 Belle Dog Adult

Now I need a query that may fetch a few records at a time and the rest of it next time. I used the following query, but this returns the first few records only.

Select * from pets limit 3;

How can I fetch 3 records first time, then 3 records second time and so on. Using "limit" only allows me to get the first x number of records.

Answer 1

The GridDB documentation shows that LIMIT value_1 [OFFSET value_2 ] is valid syntax.

In light of that revelation, it's probably something like this:

SELECT *
FROM pets
ORDER BY pets.pet_key
LIMIT 3 OFFSET 0;

Which would get the first three rows ordered by the pet_key column. To get the next three rows, you'd use:

SELECT *
FROM pets
ORDER BY pets.pet_key
LIMIT 3 OFFSET 3;