PostgreSQL – repeating rows from LIMIT OFFSET

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?

Answer

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.