Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE conversations (id INT, name VARCHAR(200), created_at DATE);
INSERT INTO conversations VALUES(1,'CONV1','1 DEC 2021');
INSERT INTO conversations VALUES(2,'CONV2','1 DEC 2021');
1 rows affected

1 rows affected

 CREATE TABLE messages (id INT, content VARCHAR(200), created_at DATE, conversation_id INT);
INSERT INTO messages VALUES(1,'TEST 3', '12 DEC 2021',1);
INSERT INTO messages VALUES(2,'TEST 2', '11 DEC 2021',1);
INSERT INTO messages VALUES(3,'TEST 1', '10 DEC 2021',1);
INSERT INTO messages VALUES(4,'TEST CONV2 1', '10 DEC 2021',2);
1 rows affected

1 rows affected

1 rows affected

1 rows affected

with Latest_Conversation_Messages as
( 
  select conversation_id,content,created_at , row_number()over(partition by conversation_id order by created_at desc)rn 
  from messages
)
select  conversation_id,content as last_message_content,created_at as last_message_at  from Latest_Conversation_Messages where rn=1


conversation_id
last_message_content
last_message_at


1
TEST 3
2021-12-12

2
TEST CONV2 1
2021-12-10


with Latest_Conversation_Messages as
( 
  select conversation_id,content,created_at , row_number()over(partition by conversation_id order by created_at desc)rn 
  from messages m
  where exists
              (
              select 1 from conversations c where c.id=m.conversation_id
              )
)
select  conversation_id,content as last_message_content,created_at as last_message_at  from Latest_Conversation_Messages where rn=1


conversation_id
last_message_content
last_message_at


1
TEST 3
2021-12-12

2
TEST CONV2 1
2021-12-10