Postgres - Combining these two queries

Question

My first query returns the first 10 message ids:

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]

Using each message_id, my second query gets the message_id with the MAX value of each of their Linear trees:

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)

Combining these two queries only gets one row which is the max value of the linear tree of the last message_id of 23. How would I get all rows?

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)

manabu · Accepted Answer · 2019-06-14 07:58:55Z

1

If you want to get the last message of each, I think you should extract data without children.

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

answered Jun 14, 2019 at 7:58

manabu

4711 gold badge5 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - Combining these two queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related