There is a table of posts and comments . It is necessary (one select) to select posts and 3 comments to each post.

 SELECT p.id, p.userID, p.title, GROUP_CONCAT(c.id, c.text) LEFT JOIN comments c FORCE INDEX(IDX_comments_postID) ON p.id = c.postID; GROUP BY p.id;

This select returns everything you need, but the number of comments should be <= 3 for each post. How can this be organized?

UPDATE: Sergey Moiseenko issued what is needed, the finished version is as follows:

 SELECT p.id, p.userID, p.title, GROUP_CONCAT(c.id) FROM posts p LEFT JOIN -- тут магия (SELECT IF(c.postID = @i, @n := @n + 1, @n := 1) AS a, @i := c.postID AS postID, c.id, c.text FROM (SELECT * FROM comments c ORDER BY c.id DESC) c, (SELECT @n := 0, @i := 0) x ORDER BY c.postID ASC) c ON p.id = c.postID WHERE a <= 3 OR a IS NULL GROUP BY p.id;
  • And the group from which side there? - Anatol
  • Records are jointed, duplicates appear for posts (if there are several comments), I remove them by grouping .... GROUP_CONCAT grouped entries (comments) give what is wrong in this? The question is how to limit their number - Max32Nov
  • HAVING COUNT(c.id) <= 3 possible - Alexey Shimansky
  • No, HAVING will return to me entries where 3 or less comments. If the comment is 4 or 5 or 100500 - I need to cut 3 pieces of them out of them, and not to ignore them - Max32Nov

2 answers 2

Did not check.

 SELECT p.id, p.userID, p.title, GROUP_CONCAT(c.id, c.text) posts p LEFT JOIN (select IF(postID=@i,@n:=@n+1,@n:=1) a, @i:=postID, c.id, c.text from comments c, (select @n:=0,@i:=0) x order by postID) c ON p.id = c.postID where a <=3 or a is null GROUP BY p.id;

On SQL it would be something like this:

 SELECT p.id, p.userID, p.title, c.id, c.text FROM posts p LEFT JOIN ( SELECT id, post_id, text FROM ( SELECT id, post_id, text, row_num = ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY id) FROM comments ) top_comments WHERE row_num <= 3 ) c ON c.post_id = p.id

In MySQL, as far as I know, there is no ROW_NUMBER, but it can be emulated as described here: https://preilly.me/2011/11/11/mysql-row_number/

  • one
    row_number () cannot be used in WHERE. Well, one table is not enough for join'a. - Sergey Moiseenko
  • Yes indeed, thanks. Corrected the answer - Aleksei