Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--https://stackoverflow.com/q/78353815/5298879
create table purchase_message (id,parent_message_id,sent_date) as values
 (41,null,'2024-02-02'::date)
,(42,null,'2024-02-03')
,(43,null,'2024-02-04')
,(44,null,'2024-02-05')
,(45,null,'2024-02-06')
  
,(51,41,'2024-03-02')
,(52,42,'2024-03-03')
,(53,43,'2024-03-04')
,(54,44,'2024-03-05')
,(55,45,'2024-03-06')
  
,(61,51,'2024-04-02')
,(62,52,'2024-04-03')
,(63,53,'2024-04-04')
,(64,54,'2024-04-05')
,(65,55,'2024-04-06')
;

SELECT 15


--OP
select rid 
from(
    values (41), (42), (43), (44)
    ) s(rid)
where rid in 
(
with recursive messages as (
    select
        rid as "root_id",
        parent.id,
        parent.sent_date
    from
        purchase_message parent
    where
        parent.id = rid
union
    select
        rid as "root_id",
        child.id,
        child.sent_date
    from
        purchase_message child
    join messages on
        child.parent_message_id = messages.id 
    ),
ordered_root_ids as (
        select
        m."root_id",
        max(m."sent_date") over (partition by m."root_id") as "maxSentDate"
    from
        messages m
            order by "maxSentDate" desc
    )
    select "root_id" from ordered_root_ids
    fetch first 2 rows only
)


rid


41

42

43

44



SELECT 4


--OP, simplified
select rid 
from(values (41), (42), (43), (44) ) s(rid)
where rid in 
(   with recursive messages as (
        select rid as "root_id",
               parent.id,
               parent.sent_date
        from purchase_message parent
        where parent.id = rid
        union
        select rid as "root_id",
               child.id,
               child.sent_date
        from purchase_message child
        join messages
          on child.parent_message_id = messages.id 
        )
    select  m."root_id"
    from messages m
    order by  max(m."sent_date") over (partition by m."root_id") desc
    fetch first 2 rows only
);


rid


41

42

43

44



SELECT 4


--fixed
with inputs(rid) as (values (41), (42), (43), (44) )
select rid 
from inputs join
(   with recursive messages as (
        select parent.id as "root_id",
               parent.id,
               parent.sent_date
        from purchase_message parent
        where parent.id in (select rid from inputs)
        union
        select parent."root_id",
               child.id,
               child.sent_date
        from purchase_message child
        join messages parent
          on child.parent_message_id = parent.id 
        )
    select m."root_id" as rid
    from messages m
    group by m."root_id"
    order by max(m."sent_date") desc
    fetch first 2 rows only
)q using(rid);


rid


43

44



SELECT 2


--another way, by accepting inputs as an array
select rid 
from(select array[41,42,43,44]) s(rids)
   ,unnest(rids)_(rid)
where rid in 
(   with recursive messages as (
        select id as "root_id",
               parent.id,
               parent.sent_date
        from purchase_message parent
        where parent.id=any(array[41,42,43,44])
        union
        select messages."root_id",
               child.id,
               child.sent_date
        from purchase_message child
        join messages
          on child.parent_message_id = messages.id 
        )
    select m."root_id"
    from messages m
    group by m."root_id"
    order by max(m."sent_date") desc
    fetch first 2 rows only
);


rid


43

44



SELECT 2


--here's a simpler illustration of what you did
create table test(a) as values(1),(2),(3);

select * from (values(1),(2),(3))_(b)
where b in (select a from test where a=b limit 1);

SELECT 3




b


1

2

3



SELECT 3


--OP, simplified
with inputs(rid) as (values (41), (42), (43), (44) )
select * 
from inputs cross join lateral 
(   with recursive messages as (
        select rid as "root_id",
               parent.id,
               parent.sent_date
        from purchase_message parent
        where parent.id = rid
        union
        select rid as "root_id",
               child.id,
               child.sent_date
        from purchase_message child
        join messages
          on child.parent_message_id = messages.id 
        )
    select *,max(m."sent_date") over (partition by m."root_id")
    from messages m
    order by  max(m."sent_date") over (partition by m."root_id") desc
    fetch first 2 rows only
);


rid
root_id
id
sent_date
max


41
41
41
2024-02-02
2024-04-02

41
41
51
2024-03-02
2024-04-02

42
42
42
2024-02-03
2024-04-03

42
42
52
2024-03-03
2024-04-03

43
43
43
2024-02-04
2024-04-04

43
43
53
2024-03-04
2024-04-04

44
44
44
2024-02-05
2024-04-05

44
44
54
2024-03-05
2024-04-05



SELECT 8
rid	root_id	id	sent_date	max
41	41	41	2024-02-02	2024-04-02
41	41	51	2024-03-02	2024-04-02
42	42	42	2024-02-03	2024-04-03
42	42	52	2024-03-03	2024-04-03
43	43	43	2024-02-04	2024-04-04
43	43	53	2024-03-04	2024-04-04
44	44	44	2024-02-05	2024-04-05
44	44	54	2024-03-05	2024-04-05