Cursor Pagination

Get the article list order by its score

Assume that we have an article table with the following structure

• id
• article_score
• status

The first page of the cursor pagination

We can order the article_score desc to get the better score from the article. And set the limit to the 3 on the every page.

select * 
from article
where status = 'published'
order by article_score desc, id asc
limit 3

The first page of the article that order by article_score desc will get the following records.

Get the next page of the cursor pagination

The article_score and id of the last record on the first page are 30 and 8. Then the next page of the article should be one of the following criteria:

1. article_score less than 30
2. article_score equal to 30 but the id is greater then 8

select * 
from article
where ( article_score < 30 or (article_score = 30 and id > '8'))
	and status = 'published'
order by article_score desc, id asc
limit 3

The next page of the article that order by article_score desc will get the following records.

Reference

• don’t use “offset” in your SQL - YouTube
• The best database pagination technique is … - DEV Community
• SQL for pagination queries - memory and performance - YouTube
• SQL for pagination queries - advanced options - YouTube