CODEWITHSUNDEEP
sqlpostgresql
Rome Torres
Rome Torres

Reputation: 1091

SQL how can I get latest row if a specific condition is matched

I am new to SQL but what I am trying to do is a comment reply feature . For instance lets say you have a profile_id of 11 and you commented a post with a post_id of 339 . If several other people commented on that post then I want to show you the latest comment on that post since you commented on it. Look at the first image below that is the table as you can see user 11 posted in the middle . I would like to get the last comment on that post that states blah blah since user 11 comments on it.

This is the table enter image description here If I am user 11 I want to only see the latest comment posted on the same thread/post that I commented on . Which means that I want to see the post that says "Blah Blah" below however I am only getting my comment . Any suggestions would be great. I am using Postgres 9.6 enter image description here

Upvotes: 0

Views: 40

Answers (2)

BritishWerewolf
BritishWerewolf

Reputation: 3968

SELECT * FROM comment_replies ORDER BY last_reply_timestamp DESC LIMIT 1;

This will order them by newest to oldest using Order By. It will also only show 1 result because of LIMIT 1

Upvotes: 0

rafaedez
rafaedez

Reputation: 56

Next query will resolve your question:

select cr.id, cr.stream_id, cr.comments
from comment_replies cr
where 
cr.id in ( select cr2.id 
           from comment_replies cr2
           where 
           cr2.last_reply_timestamp = (select  max(cr3.last_reply_timestamp)
                                       from comment_replies cr3      
                                       where cr3.stream_id=cr2.stream_id
                                      )
           and cr2.stream_id=cr.stream_id
        )
and cr.stream_id in (select cr2.stream_id 
                     from comment_replies cr2 
                     where cr2.profile_id=11)

Upvotes: 1

Related Questions