how to get last known contiguous value in postgres ltree field?

I have a child table called wbs_numbers. the primary key id is a ltree

A typical example is

id	series_id
abc.xyz.00001	1
abc.xyz.00002	1
abc.xyz.00003	1
abc.xyz.00101	1

so the parent table called series. it has a field called last_contigous_max.

given the above example, i want the series of id 1 to have its last contigous max be 3

can always assume that the ltree of wbs is always 3 fragment separated by dot. and the last fragment is always a 5 digit numeric string left padded by zero. can always assume the first child is always ending with 00001 and the theoretical total children of a series will never exceed 9999.

If you think of it as gaps and islands, the wbs_numbers will never start with a gap within a series. it will always start with an island.

meaning to say this is not possible.

id	series_id
abc.xyz.00010	1
abc.xyz.00011	1
abc.xyz.00012	1
abc.xyz.00101	1

This is possible

id	series_id
abc.xyz.00001	1
abc.xyz.00004	1
abc.xyz.00005	1
abc.xyz.00051	1
abc.xyz.00052	1
abc.xyz.00100	1
abc.xyz.10001	2
abc.xyz.10002	2
abc.xyz.10003	2
abc.xyz.10051	2
abc.xyz.10052	2
abc.xyz.10100	2
abc.xyz.20001	3
abc.xyz.20002	3
abc.xyz.20003	3
abc.xyz.20004	3
abc.xyz.20052	3
abc.xyz.20100	3

so the last max contiguous in this case is

for series id 1 => 1
for series id 2 => 3
for series id 3 => 4

What's the query to calculate the last_contigous_max number for any given series_id?

I also don't mind having another table just to store "islands".

Also, you can safely assume that wbs_number records will never be deleted once created. The id in the wbs_numbers table will never be altered once filled in as well.

Meaning to say islands will only grow and never shrink.

Solution

You can carry out your problem following these steps:

extract your integer value from your "id" field
compute a ranking value sided with your id value
filter out when your ranking value does not match your id value
get tied last row for each of your matches

WITH cte AS (
    SELECT *, CAST(RIGHT(id_, 4) AS INTEGER)  AS idval
    FROM tab
), ranked AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY series_id ORDER BY idval) AS rn
    FROM cte
)
SELECT series_id, idval
FROM ranked
WHERE idval = rn
ORDER BY ROW_NUMBER() OVER(PARTITION BY series_id ORDER BY idval DESC)
FETCH FIRST ROWS WITH TIES

Check the demo here.