Postgres CASE in ORDER BY using an alias
Try to wrap it as a subquery:
SELECT * FROM ( SELECT users.id, GREATEST( COALESCE(MAX(messages.created_at), '2012-07-25 16:05:41.870117'), COALESCE(MAX(phone_calls.created_at), '2012-07-25 16:05:41.870117') ) AS latest_interaction FROM users LEFT JOIN messages ON users.id = messages.user_id LEFT JOIN phone_calls ON users.id = phone_calls.user_id GROUP BY users.id) SubORDER BY CASE WHEN( latest_interaction > '2012-09-05 16:05:41.870117') THEN 0 WHEN(latest_interaction > '2012-09-04 16:05:41.870117') THEN 2 WHEN(latest_interaction > '2012-09-04 16:05:41.870117') THEN 3 ELSE 4 ENDLIMIT 5;
The PG manual says the ORDER BY expression:
Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.
The sub-query solution from @Mahmoud will work, or you can create the ORDER BY using the original columns messages.created_at or phone_calls.created_at