Re: Recursive CTE trees + Sorting by votes

Just export the order from your CTE.

WITH RECURSIVE tree AS (
    SELECT dr.id,
           ...,
           array[dr.id] as path,
           1 as depth,
           row_number() over (order by dr.num_votes desc) as sort_order
    FROM discussion_response AS dr
    WHERE dr.reply_parent_id IS NULL
      AND dr.discussion_id = 2763

    UNION ALL

    SELECT dr.id,
           ...,
           tree.path || dr.id,
           tree.depth + 1
           row_number() over (order by dr.num_votes desc)
    FROM discussion_response AS dr
    JOIN tree ON tree.id = dr.reply_parent_id
    WHERE NOT array[dr.id] <@ tree.path
)
SELECT *
FROM tree
ORDER BY depth, sort_order
LIMIT 50;