mysql - Paginated query from Database

Question

I have to do paginated query to return the list to frontend. And this query should be sorted in descending order of their timestamp of transaction i.e. most recent recent transaction should be the 1st element in the list and oldest transaction should be the last element in the list. But I am facing an issue :

Scenario :

Total no of records : 41
Page Size : 5
Query1 : 
        R1,R2,R3,R4,R5
Query2 :
        R6,R7,R8,R9,R10


I have served the top 10 records in two pages and now I have to return the next set of results for 
3rd page and parallely a new transaction has been done. So the total no of records is 42 now, and 
I will do sorting as per timestamp, and will end up in returning R10 again in page no 3, as now there will
be a new record in the top. Also I won't be able to show the newest record as I have already served the 
1st page.

How to solve this scenario? The DB is MySQL/MongoDB. Sometimes I need to serve this from ElasticSearch as well.

question from:https://stackoverflow.com/questions/65897191/paginated-query-from-database

Answer 1

Waitting for answers