I have a posts table and a comments table and I'm trying to order the posts by max created_at of the comments (or created_at for the post if no comments). How can I make this query use posts.created_at instead of max_comment_created_at if there are no comments for the post?
SELECT posts.*, case
max_comment_created_at is null or posts.created_at >= max_comment_created_at
end as selected_created_at
left join (
select post_id, max(created_at) as max_comment_created_at from comments group by post_id
) as post_comments on post_comments.post_id = posts.id
ORDER BY selected_created_at desc