Mysql pagination without offset

Vote count:

0

Let's assume I have got a table with these data:

I want to order it by createdAt column DESC, and id column DESC

select id, createdAt from post order by createdAt desc, id desc

Now it looks like this:

I want to paginate it with 2 items per page and don't want to use offset, just limit:

select id, createdAt from post order by createdAt desc, id desc limit 2

To get the next 2 items i use this query:

SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2

I can go on like this. Get the last item's createdAt and id then use it for next page query.

But i'm trying to formulate the previous page query for almost two days and can't figure out a way yet.

Here is what i tried already:

Get the first item from current result (instead of last) use it's id and createdAt field and reverse the conditions in query (createdAt >=, id >, created at >). But this query always gives me the first two results (which is normal because the rows providing this conditions are the first two).

I'm already out of ideas. I need help. Thanks.

asked 1 min ago

cnkt

1,100

Mysql pagination without offset