CODEWITHSUNDEEP
mysqlsqlpostgresqlsql-order-by
JoshEmory
JoshEmory

Reputation: 644

Sort by two columns with conditionals?

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.

Upvotes: 0

Views: 82

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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.

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.

Upvotes: 0

winmutt
winmutt

Reputation: 405

Case statements will not make efficient use of indexes.

ORDER BY priority_N IS NULL DESC, priorit_n ASC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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

Upvotes: 1

Related Questions