I hope that title is clear. If not, please change.

I have table, which look like this:

enter image description here

I would like to create a select query, where if gid is in anywhere in ancestor column, select max level from this rows. For example: Gid is 727 is four times in ancestor column. Max level from this rows is 3, so the value in the new column, let's says level_a, where gid is 727, will be 3. For gid 725 the same but for gid 717 will be 0.

I tried to use unnest, join, subquery but nothing worked. I have read about lateral join but I don't have any idea, how I can use it.

I'm using PostgreSQL 10 with PostGIS 2.5.2


Unnest is working when I try:

select id, coalesce(sub.level,0) maxlevel
from sometable
left join (
    select distinct on (1) 
        unnest(ancestor) id2, level
    from sometable
    order by 1,2 DESC) sub
on sometable.id = sub.id2

enter image description here

  • 1
    It works! Thanks!
    – Michal De
    Sep 6 '19 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.