GROUP BY on multiple joins

Question

I have searched for the issue but could not have a working solution.

I have 3 tables: User, Post, Comments.

select u.id user
     , p.id post
     , c.id comm
  from USer u
  join Post p
    on u.id = p.user_id
  join Comments c
    on p.id = c.post_id;

This gives me an output with 3 columns that relates a user with the posts they have and the comments received on each.

The o/p is like:

user    post    comm
1    1    4
1    1    5
1    1    7
1    1    8
2    5    11
2    5    12
2    7    13

I wanted to find the user with the maximum number of overall comm. For this the GROUP BY on user is not working. What could be an alternative for the task?

"is not working" is not helpful. Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) But-- — philipxy, Jun 19 '19 at 22:46
Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — philipxy, Jun 19 '19 at 22:49

score 0 · Answer 1 · answered Jun 19 '19 at 21:18

You can use GROUP BY, ORDER BY, and LIMIT:

select p.user_id, count(*) as num_comments
from Post p inner join
     Comments c
     on p.id = c.post_id
group by p.user_id
order by num_comments desc
limit 1;

Note that because you only want the user id, you do not need the user table, because user_id is in post.

GROUP BY on multiple joins

1 Answers1