# Postgres defaults to ID DESC, MySQL defaults to ID ASC **URL:** \ **Category:** Rails\ **Created:** [August 19, 2009, 2:01am UTC](https://www.ruby-forum.com/t/postgres-defaults-to-id-desc-mysql-defaults-to-id-asc/173981 "2009-08-19T02:01:16Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [August 19, 2009, 2:01am UTC](https://www.ruby-forum.com/t/postgres-defaults-to-id-desc-mysql-defaults-to-id-asc/173981/1 "2009-08-19T02:01:16Z")
I just switched to Postgres and some of my tests are failing. It seems that Postgres defaults by ordering data by ID DESC, whereas MySQL defaults to ordering by ID ASC. What is the best practice for handling this? Is there a setting in Postgres you can change? Should I be explicitly specifying the order of my data in my queries? In various places throughout my application I am doing things like: User.first This obviously is not good with the order of the data is not consistent. I also feel like the first User should return the first user, not the last one created. What do you think? ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [August 19, 2009, 2:10am UTC](https://www.ruby-forum.com/t/postgres-defaults-to-id-desc-mysql-defaults-to-id-asc/173981/2 "2009-08-19T02:10:38Z")
On Tue, Aug 18, 2009 at 5:01 PM, Ben Johnson[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > User.first > > This obviously is not good with the order of the data is not consistent. > I also feel like the first User should return the first user, not the > last one created. The Rails tests use explicit ordering to work around this discrepancy. User.first without an ordering is technically undefined. It could be any row. Since you’re usually doing account.users.first, you could :order =\> ‘id asc’ on the has\_many. jeremy ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [August 19, 2009, 2:27am UTC](https://www.ruby-forum.com/t/postgres-defaults-to-id-desc-mysql-defaults-to-id-asc/173981/3 "2009-08-19T02:27:57Z")
Ben J. wrote: > I just switched to Postgres and some of my tests are failing. It seems > that Postgres defaults by ordering data by ID DESC, whereas MySQL > defaults to ordering by ID ASC. As Jeremy correctly pointed out, without an ORDER clause, your records may be returned in any order. […] > In various places throughout my application I am doing things like: > > User.first > > This obviously is not good with the order of the data is not consistent. > I also feel like the first User should return the first user, not the > last one created. There is no guarantee of that – without the ORDER clause, the database is free to return the first user created, the last user created, or one somewhere in the middle. User.first just returns whichever user the DB decided to retrieve first. If you need a specific ordering, you need an order clause. > What do you think? I think you don’t quite understand how SQL databases retrieve records. ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [August 19, 2009, 4:55pm UTC](https://www.ruby-forum.com/t/postgres-defaults-to-id-desc-mysql-defaults-to-id-asc/173981/4 "2009-08-19T16:55:43Z")
(My point of view) Order the result by ID is also a bad idea, if you switch to another ID schema (for example UUID, descending sequence, etc.) you must rewrite a lot of code. The ID is something the application must not “know”, order for example by name, by a created\_at field (and that have more sense if you want “the last created user”). Regards. Franco C… On Aug 18, 9:01 pm, Ben J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![benjohnson](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/3ec8ea/32.png) [@benjohnson](https://www.ruby-forum.com/u/benjohnson)\ **Post date:** [August 19, 2009, 3:13am UTC](https://www.ruby-forum.com/t/postgres-defaults-to-id-desc-mysql-defaults-to-id-asc/173981/5 "2009-08-19T03:13:31Z")
Marnen Laibow-Koser wrote: > > What do you think? > > I think you don’t quite understand how SQL databases retrieve records. Think of it this way: select \* from users; returns a record “set.” As in, “unordered set” as apposed to a record “array.” An id field is just another column that happens to be used to identify rows. The only requirement (assuming id is used as a primary key) is that each value is unique. The sequence of those values is undefined. Therefore, the order of the record set is undefined. An “order by” clause is applied to the resulting set before the set is returned, which essentially turns it into an “ordered set/array.” This final result is stored in an Array object by ActiveRecord (another ordered storage object) so can be depended upon to be kept in order.