PostgreSQL - repeating rows from LIMIT OFFSET

limit, offset, postgresql, sql, sql-order-by

Solution

Why does "foo" appear in both queries?

Because all rows that are returned have the same value for the `status` column. In that case the database is free to return the rows in any order it wants.

If you want a reproducable ordering you need to add a second column to your order by statement to make it consistent. E.g. the ID column:

SELECT students.* 
FROM students 
ORDER BY students.status asc, 
         students.id asc

If two rows have the same value for the status column, they will be sorted by the id.

Problem

I noticed some repeating rows in a paginated recordset. When I run this query: ``` SELECT "students".* FROM "students" ORDER BY "students"."status" asc LIMIT 3 OFFSET 0 ``` I get: ``` | id | name | status | | 1 | foo | active | | 12 | alice | active | | 4 | bob | active | ``` Next query: ``` SELECT "students".* FROM "students" ORDER BY "students"."status" asc LIMIT 3 OFFSET 3 ``` I get: ``` | id | name | status | | 1 | foo | active | | 6 | cindy | active | | 2 | dylan | active | ``` Why does "foo" appear in both queries?

Original source