postgresql, odd OFFSET/LIMIT behavior ( records order )

Question

so basically I have this scope(sql):

scope.to_sql
=> "SELECT  \"offers\".* FROM \"offers\" INNER JOIN \"cities_offers\" ON \"offers\".\"id\" = \"cities_offers\".\"offer_id\" WHERE \"cities_offers\".\"city_id\" = 2 AND \"offers\".\"category_id\" IN (2) AND (offers.category_id is NOT NULL) ORDER BY offers.discount desc LIMIT 25 OFFSET 0"

Somehow the records order is different for the above query and the same one without LIMIT and OFFSET:

   scope[6]
=> #<Offer id: 8629 ...

scope.except(:offset, :limit)[6]
=> #<Offer id: 8729 ...

both 8629 and 8729 records have the same discount value ( the attribute I order by ).

Could you please advice if it's possible to keep the same records ordering under these circumstances?

mu is too short · Accepted Answer · 2012-02-22 19:27:19Z

7

Relational databases are set-based and hence inherently unordered; the order of the records in a result set is specified only by the ORDER BY clause. If two rows have the same values for the expression in the ORDER BY clause, then running the same query twice may return those rows in different positions; altering the query by adding LIMIT and OFFSET just makes things worse by making a different order more likely.

If you want the database to give you rows in a certain order, you must fully specify the order in your ORDER BY clause. You have to add more to the order call in your scope:

...order('offers.discount desc, offers.created_at asc')

or something like that depending on the specific order that you need.

answered Feb 22, 2012 at 19:27

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql, odd OFFSET/LIMIT behavior ( records order )

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related