Search code examples
postgresqlamazon-web-servicesamazon-rds

AWS RDS query returning inconsistent results

Running into the strangest issue with AWS RDS Serverless. I'm using the Data API to query the database. (Note: id is a primary key)

SELECT COUNT(*) FROM accounts;

= 28535

My API limits page size to 200 and these are the queries I'm making to the database.

SELECT id FROM accounts ORDER BY last_name ASC LIMIT 200 OFFSET 3600;

== contains a row with id 92778

Then the next page (OFFSET 3800),

SELECT id FROM accounts ORDER BY last_name ASC LIMIT 200 OFFSET 3800;

== ALSO contains a row with id 92778

What's causing this?

Solution

  • Yeah. Realized my mistake after I posted the question.

    SELECT id FROM accounts ORDER BY last_name, id ASC LIMIT 200 OFFSET 3600;

    There's no guarantee on the order if there are multiple last_name with the same value. Added id as a secondary sort and that fixed it.