Postgres - 合并这两个查询

Question

我的第一个查询返回前10个邮件ID：

SELECT * from message WHERE $1 IN (creator_id, recipient_id) 
AND parent_id is null 
ORDER BY date_posted 
DESC FETCH FIRST 10 ROW ONLY

[1, 2, 4, 6, 10, 14, 17, 18, 19, 23]

使用每个message_id，我的第二个查询使用它们的线性树中最大值来获取每个message_id：

With RECURSIVE recursetree AS
(
  SELECT * FROM message WHERE message_id = $1
  UNION ALL
  SELECT t.*
  From message t
  JOIN recursetree rt ON rt.message_id = t.parent_id                                                        
 )
 SELECT * from recursetree where parent_id is not distinct 
 from (select max(parent_id) from recursetree)

将这两个查询合并后，只得到一个行，即最后一个message_id的线性树的最大值。我该如何获取所有行？

with RECURSIVE cte as 
(
    (
    SELECT * from message WHERE $1 IN (creator_id, recipient_id) 
    AND parent_id is null ORDER BY date_posted DESC 
        FETCH FIRST 10 ROW ONLY
    )
    UNION
    SELECT t.*
    From message t
    JOIN cte rt ON rt.message_id = t.parent_id
)
SELECT * FROM cte where parent_id is not distinct 
     from (select max(parent_id) from cte)

- DIRTY DAVE

Answer 1

如果您想获取每个消息的最后一条消息，我认为您应该提取没有子级的数据。

select m.* 
  from message m
  left join message child on m.message_id = child.parent_id
 where child.message_id is null

- manabu