0

I am trying to return users avatars for the comments they have made with a discussion.

I am using the below query:

SELECT c.id, 
       c.user_id, 
       c.discussion_id, 
       u.avatar 
FROM comments c
LEFT JOIN users u on u.user_id = c.user_id
GROUP BY c.discussion_id, u.user_id

The above works as expected, but the issue I am having is I would only like to return the first 5 users avatars per discussion. If I add LIMIT 5 to the end of the query it limits all results to 5 rather than limiting each discussions result to 5.

I have also tried the following, but again it gives me the first 5 avatars of total, instead of per discussion.

SELECT *
FROM comments c 
LEFT JOIN (  SELECT user_id,
                    avatar,
                    GROUP_CONCAT(avatar) grouped_avatar
             FROM users
             GROUP BY user_id
          ) u  on u.user_id = c.user_id
GROUP BY c.discussion_id, u.user_id
AND FIND_IN_SET(avatar, grouped_avatar) BETWEEN 1 AND 5

How can I achieve this.

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
herbie
  • 335
  • 2
  • 14
  • Check https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – Ergest Basha Mar 30 '22 at 10:47
  • I don't understand the problem. For each avatar, do you have 1 row in `users`? And you want 5 avatars for each user, right? correct me if I'm wrong – Soheil Rahsaz Mar 30 '22 at 11:17
  • If I am understanding your questions correctly, you will need to use CTE (Common table expression ), look up SQL recursion this should help you solve your problem – Demeteor Mar 30 '22 at 11:18

0 Answers0