Get latest child row with parent table row

Two tables posts and comments. posts has many comments (comments has post_id foreign key to posts id primary key)

posts

id | content
------------

comments

id | post_id | text | created_at  
-------------------------------

I need all posts, its content, and latest comment (based on max(created_at), and its text.

I can get upto created_at using this

with comment_latest as (select 
   post_id, 
   max(created_at) as latest_commented_at 
from comments 
group by 1)

select 
    posts.id, 
    posts.content, 
    comment_latest.latest_commented_at 
from posts 
left join comment_latest on comment_latest.post_id = posts.id
order by posts.id desc 
limit 10

But I want the text of the comment as well.

Answer

You can use the Postgres extension distinct on:

select distinct on (p.id) p.* c.*
from posts p left join
     comments c
     on p.id = c.post_id
order by p.id desc, c.created_at desc
limit 10;

This sorts the data by the order by clause, returning the first row based on the keys in the distinct on.

Advertisement

Answer