Ordering by max datetime of join

a9724724 · #1 (**permalink**) 11-01-11, 11:52

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?

Code:

SELECT posts.*, case
  when
    max_comment_created_at is null or posts.created_at >= max_comment_created_at
  then
    posts.created_at
  else
    max_comment_created_at
  end as selected_created_at
FROM posts
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

thanks

Edit: the query above works.

Internet Services

Web Development

Digital Marketing

Consumer Tech