CODEWITHSUNDEEP
sqlpostgresql
DIRTY DAVE
DIRTY DAVE

Reputation: 2731

Postgres - Combining these two queries

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)

enter image description here

Upvotes: 3

Views: 79

Answers (1)

manabu
manabu

Reputation: 471

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

Upvotes: 1

Related Questions