2

I have a table called books which has a integer column ordering, and created_at column which is date time.

I would like to get the null rows of ordering to come on the top with the descending order in which they are created, after that i would like to get the non null ordering rows in ascending order.

Here's the query I have tried

select * from books order by ordering asc nulls first;

I am getting null ordering rows on the top, but those are in ascending order. Is there a way I can get descending order of null rows based on created_at desc and then use the ordering column to sort the remaining rows in ascending order

The relevent Rails query is

Book.order('ordering ASC NULLS FIRST')
gates
  • 4,465
  • 7
  • 32
  • 60

1 Answers1

2

Yup, I think you're looking for:

Book.order('ordering ASC NULLS FIRST, created_at DESC')

This will sort by ordering, and then by created_at in results that have the same ordering.

Mitch
  • 516
  • 3
  • 5
  • Thanks. Doubt: so the records which already have ordering won't be touched in the second order by? – gates May 02 '20 at 07:43
  • exactly, the second order by is superseded by the first and can't "break" it. – Mitch May 02 '20 at 07:54