On Thu, Aug 7, 2014 at 8:12 AM, Vik Fearing <vik.fear...@dalibo.com> wrote: > > 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; >
It looks like this clobbers the hierarchy by sorting by depth first. I'm trying to preserve said hierarchy so I can paginate using OFFSET/LIMIT easily. I'm not sure what I'm shooting for is even possible, though. -- Greg Taylor http://gc-taylor.com
