CODEWITHSUNDEEP
ruby-on-railssql-order-by
Avdept
Avdept

Reputation: 2289

Order by nil value in column

I have table with column position, which in some cases, for some collection of records can be nil. I have default order options like order('positions ASC')

id| name | position
1    5       null
2    6       null
3    7       null

If for some collection that I sort (example above), all values have null in position column, in which order I will get this collection from db? I'm suggestion I will get collection in order of ids (1,2,3). Am I correct?

Addition #1: DB - Postgresql

Upvotes: 1

Views: 936

Answers (2)

Alejandro Babio
Alejandro Babio

Reputation: 5229

According Postgres manual, if no sorting clause the records are returned according with physical position at the disk. It says nothing for sorted records with equal values on sort fields. But, it uses b-tree and, like clasic db managers, it must return on the order stored at the b-tree. You must expect that each of this change on db reorganization.

At the end, there are no warranty on the order of records with same values on sort fields.

Note: using Postgres you can make the NULL values at the first or the last (it is detailed at the referrer link).

At this related question, I'm agree with @macek.

Upvotes: 2

Jason Cheladyn
Jason Cheladyn

Reputation: 595

You can do something like this.

Cats:

id| name | position
1    5       null
2    6       null
3    7       not_null

nil     = Cat.order("id ASC").where(position: nil) = [1, 2]
not_nil = Cat.order("id ASC").where("position is not null") = [3]

not_nil + nil = [3, 1, 2]

This preserves order.

Upvotes: 1

Related Questions