Using CASE to calculate lowest_new_id:

with cte_ids as
(
    select a.id, a.new_id, lowest_new_id = a.new_id
    from ids a
    left join ids b on a.new_id = b.id
    where b.id is null
    union all
    select c.id, c.new_id, 
           lowest_new_id = CASE WHEN d.lowest_new_id < c.new_id 
                                THEN d.lowest_new_id ELSE c.new_id 
                           END
    from ids  c
    join cte_ids d on d.id = c.new_id
)
select * 
from cte_ids;

db<>fiddle demo

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top