Why pagination sometimes has overlapping pages

Let's say you want to present a list of records, sorted by when they were created, and using pagination to show 10 records at a time.

But let's throw a wrinkle in our setup: our first 12 records all have an identical timestamp, all the way down to the exact second.

In this situation, page 1 will show 10 of those records with the exact timestamp, as expected. But when you navigate to page 2, the first 2 records you see will not necessarily be records 11 and 12. In fact, you may very well see two of the records you already saw on page 1.

Why does this happen? Why is there a seeming "overlap" of pages? When you tell a database to sort by a column, it cannot differentiate between identical values. Running the same ordered query twice could return the same records in a different order.

If you want there to be a consistent record order in a case like this, you'll need to add a second unique column to your sort order. A common choice is the primary key for the table.

Here to help,

Joel

P.S. Did you know about the Mastering Laravel Youtube channel?