ORDER BY alternative values from main- and subquery

Question

Right now I have a query that looks something like so:

SELECT id 
FROM post_table post
WHERE post.post_user_id = user_id
ORDER BY (SELECT max(comment_created_date) 
          FROM comments_table WHERE comments_post_id = post.id) DESC,
 post.post_created_date DESC

My idea for this query was that it would order a series of posts like so

Post 1:
    created Date: Jan 1 2015
    comments : []

Post 2:
     created Date: Jan 5 2015
     comments : []

Post 3:
     created Date : December 1 2014
     comments: [
         0: Created Date: Jan 6 2015
      ]

So in this case the order the posts would be returned in is

Post 3, Post 2, Post 1

Because Post 3 has a comment that is newer than any other posts, and Post 2 was created before Post 1.

But when I run the query, the posts are still all sorted by their created date and the query doesn't seem to take into account the comments created date.

Why post 2 was created before post 1? – Houari Jan 30 '15 at 20:16 — Houari, Jan 30 '15 at 20:16

score 2 · Accepted Answer · edited May 23 '17 at 11:57

It should work like this:

SELECT id 
FROM   post_table p
WHERE  post_user_id = $user_id  -- this is your input parameter
ORDER  BY GREATEST(
   (
   SELECT max(comment_created_date) 
   FROM   comments_table
   WHERE  comments_post_id = p.id
   )
 , post_created_date) DESC NULLS LAST;

You will want to add NULLS LAST if date columns can be NULL.

PostgreSQL sort by datetime asc, null first?

If comments can only be later than posts (would make sense), you can use COALESCE instead of GREATEST.

Cleaner alternative (may or may not be faster, depending on data distribution):

SELECT id 
FROM   post_table p
LEFT   JOIN  (
   SELECT comments_post_id AS id, max(comment_created_date) AS max_date
   FROM   comments_table
   GROUP  BY 1
   ) c USING (id)
WHERE  post_user_id = $user_id
ORDER  BY GREATEST(c.max_date, p.post_created_date) DESC NULLS LAST;

Since you have pg 9.3 you can also use a LATERAL join. Probably faster:

SELECT id 
FROM   post_table p
LEFT   JOIN  LATERAL (
   SELECT max(comment_created_date) AS max_date
   FROM   comments_table
   WHERE  comments_post_id = p.id
   GROUP  BY comments_post_id
   ) c ON TRUE
WHERE  post_user_id = $user_id
ORDER  BY GREATEST(c.max_date, p.post_created_date) DESC NULLS LAST;

score 0 · Answer 2 · answered Jan 30 '15 at 23:50

The other thing I would suggest, if this is going to be a heavily hit query, is to create a calculated column on the post_table of 'date_last_commented' and use a trigger to update that on any insert or update of the comments table.

Then you could run simple query of:

SELECT id 
FROM post_table post
WHERE post.post_user_id = user_id
ORDER BY post.date_last_commented DESC nulls last, post.post_created_date DESC
;

ORDER BY alternative values from main- and subquery

2 Answers2