Sorting with different date fields

I have a tricky SQL question. I am working with posts that can be sent or scheduled. Here is the structure:

----------------
- Posts        -
----------------
- id           -
- sent_at      -
- scheduled_at -
- created_at   -
----------------

• A post can be sent without开发者_JAVA百科 being scheduled.
• A scheduled post will be sent at the scheduled_at date. At this point the sent_at date will be equal to the scheduled_at date
• Some posts are just created, with no sent, or scheduled date
• All posts have a created_at date

Examples of possible data:

P1: {scheduled_at: NULL, sent_at: NULL, created_at: T}
P2: {scheduled_at: NULL, sent_at: T, created_at: T}
P3: {scheduled_at: T, sent_at: T, created_at: T}
P4: {scheduled_at: T+n, sent_at: NULL, created_at: T}
P5: {scheduled_at: T+n, sent_at: T+n, created_at: T}

What I want to do:

I want to get all posts ordered by a different attribute depending on the situation

• created_at for P1 and P2
• scheduled_at for P3, P4, and P5

I tried different variations of ORDER conditions but I can't get anything to work for all the cases. I hope this all makes sense. I can provide more details if it doesn't.

Try using coalesce:

select * from foo
order by coalesce(created_at, scheduled_at)

In P1 and P2 scheduled_at is NULL so this is the only case which should be ordered by created_at.
So you can use:
ORDER BY CASE WHEN scheduled_at IS NULL THEN created_at ELSE scheduled_at END