Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: Ordering by max datetime of join

    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.
    Last edited by a9724724; 11-01-11 at 13:47.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •