CODEWITHSUNDEEP
sqlpostgresql
WHOATEMYNOODLES
WHOATEMYNOODLES

Reputation: 2641

Postgresql - Reformat Query return order

I have a Recursive Query that returns a non linear tree, but it returns all of the rootnodes first. (where parent_id is null)

How would I order the rows in which my query gets one rootnode, gets the entire tree, followed by getting the second rootnode and then getting that entire tree...so on etc. 

With RECURSIVE cte AS
(
   (
    SELECT * 
    FROM comments 
    WHERE thread_id = 1
      AND parent_id is NULL 
    ORDER BY upvoted DESC 
    FETCH FIRST 10 ROW ONLY
   )
   UNION
   SELECT t.*
   From comments t
     JOIN cte rt ON rt.comment_id = t.parent_id
 )
  SELECT cte.comment_id, cte.date_posted, cte.posted_by, cte.posted_by_user, cte.thread_id,
            cte.parent_id, cte.comments, cte.post_title, cte.posted_to_group, cte.upvoted, cte.downvoted,
            cte.depth, cv.user_id, cv.vote, cv.thread_id
     from cte LEFT JOIN
          commentsvoted cv
          ON cte.comment_id = cv.id
          AND cv.user_id = '82411580-6355-490e-be79- e7db9f561f66'

If I add:

ORDER BY coalesce(parent_id, comment_id), comment_id

to my last select, it returns what I have described except it ignores:

ORDER BY upvoted DESC

enter image description here

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use window functions in the order by:

ORDER BY MAX(upvoted) OVER (PARTITION BY coalesce(parent_id, comment_id)) DESC, 
         coalesce(parent_id, comment_id),   -- keep this in the event of ties,
         (case when parent_id is null then 1 else 0 end),
         comment_id

Upvotes: 1

Related Questions