commit_rel_sort_select.sql

with recursive rel_tree as (
    select "hash", "parent", 1 as level, cast(array["hash"] AS varchar(512)[]) as path_info
    from "git.commits"
    where "parent" is null
    union all
    select c.hash, c.parent, p.level + 1, cast((p.path_info || c.hash) AS varchar(512)[])
    from "git.commits" c
    	join rel_tree p on c.parent = p.hash
)

select "rel_tree"."hash", "git.commits"."commit_time", "git.commits"."message" from rel_tree inner join "git.commits" on "git.commits"."hash"="rel_tree"."hash" order by array_length(path_info, 1) desc limit 5;