How do I order table columns by the most recent date?

Question

I have email_invitations table with three datetime columns, sent_at, opened_at, and rsvped_at. How do I sort the three columns by their date in ascending order (most recent at top)?

I'm using default_scope in rails:

default_scope { order('sent_at ASC' }

Here's what I have

EmailInvitation
id: 1, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-03-25"
id: 2, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-04-01"
id: 3, sent_at: "2016-03-23", opened_at: "2016-03-24", rsvped_at: nil
id: 4, sent_at: "2016-03-29", opened_at: nil, rsvped_at: nil

Using EmailInvitation.order(sent_at: :asc, opened_at: :asc, rspved_at: :asc) it looks like it sorts sent_at first, then opened_at, then rsvped_at.

id: 4, sent_at: "2016-03-29", opened_at: nil, rsvped_at: nil
id: 1, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-03-25"
id: 2, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-04-01"
id: 3, sent_at: "2016-03-23", opened_at: "2016-03-24", rsvped_at: nil

but what I really want whatever has the most recent date will be at the top so somehow I need to join all the columns so it doesn't look at the column name, just the date.

id: 2, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-04-01"
id: 4, sent_at: "2016-03-29", opened_at: nil, rsvped_at: nil
id: 1, sent_at: "2016-03-24", opened_at: "2016-03-25", rsvped_at: "2016-03-25"
id: 3, sent_at: "2016-03-23", opened_at: "2016-03-24", rsvped_at: nil

Since you are not satisfied with Athonys answer: can you show some sample data and the expected result? — Pascal, Mar 28 '16 at 18:30

score 2 · Accepted Answer · answered Mar 28 '16 at 19:17

Presumably:

sent_at will never be nil.
If opened_at is nil then so is rsvped_at.
sent_at will always be at most opened_at or opened_at will be nil.
If opened_at is not nil then it will always be at most rsvped_at or rsvped_at will be nil.

That means that you're really trying to sort by the first non-nil entry in

[rsvped_at, opened_at, sent_at]

Right? That's that same as trying to pick the first non-NULL entry in that list and sorting by that. That's pretty simple to express using COALESCE:

order('coalesce(rsvped_at, opened_at, sent_at) desc')

The SQL COALESCE function simply returns the first (from left to right) of its arguments that is not NULL.

How do I order table columns by the most recent date?

1 Answers1