postgres hierarchy - count of child levels and sort by date of children or grandchildren

create table mytable ( id serial primary key, parent_id int references mytable, postdate date );create index mytable_parent_id_idx on mytable (parent_id);insert into mytable (id, parent_id, postdate) values (1, null, '2015-03-10');insert into mytable (id, parent_id, postdate) values (2, 1, '2015-03-11');insert into mytable (id, parent_id, postdate) values (3, 1, '2015-03-12');insert into mytable (id, parent_id, postdate) values (4, 3, '2015-03-13');insert into mytable (id, parent_id, postdate) values (5, null, '2015-03-14');insert into mytable (id, parent_id, postdate) values (6, null, '2015-03-15');insert into mytable (id, parent_id, postdate) values (7, 6, '2015-03-16');with recursive recu as (  select id as parent, id as root, null::date as child_postdate    from mytable   where parent_id is null   union all  select r.parent, mytable.id, mytable.postdate    from recu r    join mytable      on parent_id = r.root)select m.id, c.cnt, m.postdate, c.max_child_date  from mytable m  join ( select parent, count(*) as cnt, max(child_postdate) as max_child_date           from recu          group by parent       ) c on c.parent = m.id order by c.max_child_date desc nulls last, m.postdate desc;

You'll need a recursive query to count the elements in the subtrees:

WITH RECURSIVE opa AS (        SELECT id AS par        , id AS moi        FROM the_tree        WHERE parent_id IS NULL        UNION ALL        SELECT o.par AS par        , t.id AS moi        FROM opa o        JOIN the_tree t ON t.parent_id = o.moi        )SELECT t.id        , c.cnt        , t.postdateFROM the_tree tJOIN ( SELECT par, COUNT(*) AS cnt        FROM opa o        GROUP BY par        ) c ON c.par = t.idORDER BY t.id        ;

UPDATE (it appears the OP also wants the maxdate per tree)

        -- The same, but also select the postdate        -- --------------------------------------WITH RECURSIVE opa AS (        SELECT id AS par        , id AS moi        , postdate AS postdate        FROM the_tree        WHERE parent_id IS NULL        UNION ALL        SELECT o.par AS par        , t.id AS moi        -- , GREATEST(o.postdate,t.postdate) AS postdate        , t.postdate AS postdate        FROM opa o        JOIN the_tree t ON t.parent_id = o.moi        )SELECT t.id        , c.cnt        , t.postdate        , c.maxdateFROM the_tree tJOIN ( SELECT par, COUNT(*) AS cnt        , MAX(o.postdate) AS maxdate -- and obtain the max()        FROM opa o        GROUP BY par        ) c ON c.par = t.idORDER BY c.maxdate, t.id        ;

After looking at everyone's code, I created the subquery I needed. I can use PHP to vary the 'case when' code depending on the user's sort selection. For instance, the code below will sort the root nodes based on child level 1's postdate.

with recursive cte as (select id as parent, id as root, null::timestamp as child_postdate,0 as depthfrom mytablewhere parent_id = -1union allselect r.parent, mytable.id, mytable.postdate,depth+1from cte rjoin mytable  on parent_id = r.root)select m.id, c.cnt, m.postdatefrom ssf.dtb_021 mjoin ( select parent, count(*) as cnt, max(child_postdate) as max_child_date,depth       from cte       group by parent,depth     ) c on c.parent = m.idorder by   case    when depth=2 then 1    when depth=1 then 2    else 0  end DESC,c.max_child_date desc nulls last, m.postdate desc;