CODEWITHSUNDEEP
sqlpostgresql
saheed
saheed

Reputation: 13

Group by on join and calculate max of groups

I have 3 tables as described below:

All three tables and output image

posts post table

post_comments posts comments

comments comments

Now I want to fetch the posts that have highest liked comments and the status of that comment should be active in Postgres.

OUTPUT:

posts resultant posts

NOTE: Since for post 1, the highest liked comment is inactive.

I've tried something like this:

select "posts".*
from "posts" 
inner join (select id, max(likes) l from comments innner join post_comments on comments.id = post_comments.alert_id and post_comments.post_id = posts.id) a on posts.id = a.cid ...

This is not complete but I'm unable to do this.

Upvotes: 1

Views: 53

Answers (2)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6289

Try something like this:

SELECT posts.*, MAX(likes) l
FROM posts
  JOIN post_comments ON post_id = posts.id
  LEFT JOIN comments ON comment_id = comments.id
GROUP BY posts.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

In Postgres, you can get the active comment with the most likes for each post using distinct on:

select distinct on (pc.post_id) pc.*
from post_comments pc join
     comments c
     on pc.comment_id = c.id
where c.status = 'active'
order by pc.post_id, c.likes desc;

I think this is quite related to what you want.

Upvotes: 3

Related Questions