Sort by two columns with conditionals?

Question

I have a table with a column priority_n. Pretend there are 5 items in that table. Two with nil as priority_n, and the other three have 1, 2, 3.

I'd like to do a where(priority_n: nil).order(published_at: :desc) combined with where.not(priority_n: nil).order(priority_n: :asc). I want the nil ones at the beginning of the active record relations, and then the prioritized ones after them. Is there a way to do this?

If I could figure out how to do this in SQL then I could do it in rails.

Answer 1

The following is the order by clause in standard SQL:

order by (case when priority_n is null then 0 else 1 end),
         priority_n asc

Answer 2

Case statements will not make efficient use of indexes.

ORDER BY priority_N IS NULL DESC, priorit_n ASC 

Answer 3

In PostgreSQL, sorting nulls first / last is dead simple with the (standard SQL!) NULLS FIRST | LAST:

ORDER BY priority_n NULLS FIRST, published_at

The second ORDER BY item, because it seems you want to order rows with the same priority_n according to published_at.

• PostgreSQL sort by datetime asc, null first?
• ORDER BY DATE showing NULLS first then most recent dates

MySQL does not implement NULLS FIRST | LAST. Substitute with:

ORDER BY priority_n IS NOT NULL, priority_n, published_at

Would work in Postgres, too.
priority_n IS NOT NULL is a boolean expression that evaluates to FALSE (0) or TRUE (1). 0 sorts before 1 (and both before NULL, but not relevant here.), so rows with priority_n IS NULL come first.

• ORDER BY ASC with Nulls at the Bottom
• MySQL Order by column = x, column asc?