SELECT 3 comments for each post

Question

I have a table comments

id | comment | post_id |
1     Hey       1
2     Hey       2
3     Hey       2
4     Hey       3
5     Hey       1

I want to get 3 comments for each posts.

I did something like this below.

"select * from comments WHERE post_id IN(1,2,3) LIMIT 3"

But this yields me only 3 rows.

But i want max 3 rows for each post_id not total 3 rows.

Any help where i am going wrong ?

This is called "greatest N per group". It is frequently asked and not particularly straightforward, and I'm searching for a good existing answer to point you toward... — Michael Berkowski, Jan 07 '15 at 18:47
Ah, [here it is-- an excellent answer by Bill Karwin](http://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category/1442867#1442867) — Michael Berkowski, Jan 07 '15 at 18:49
@RockFellerDumbstep radar's answer below should be exactly the implementation you need. It expresses the same thing as Bill's linked answer with slightly different syntax. — Michael Berkowski, Jan 07 '15 at 18:52
RockFellerDumbstep about JOIN @Michaelberkowski latest comment — violator667, Jan 07 '15 at 18:52

score 1 · Accepted Answer · answered Jan 07 '15 at 18:47

1

it is like doing partition based on post_id and then doing selection of 3 elements in that partition you can achieve this using mysql variables

select id, comment, post_id
from 
(
SELECT id, comment, post_id, @row_number:=CASE WHEN @post_id=post_id THEN @row_number+1 ELSE 1 END AS row_number,
       @post_id:=post_id AS varval
FROM comments
join (select @row_number := 0, @post_id := NULL ) as var
order by post_id, id 
) t
where t.row_number <=3

answered Jan 07 '15 at 18:47

radar

13,270
2
25
33

#1054 - Unknown column 'id' in 'field list' – RockFeller Dumbstep Jan 07 '15 at 18:51
Your table posted at the top has a `comments.id` column. This should be right on track. – Michael Berkowski Jan 07 '15 at 18:52
http://sqlfiddle.com/#!2/d7de45/2 It should work as is - I added a couple more example rows to `post_id = 1` to show that those above 3 are eliminated – Michael Berkowski Jan 07 '15 at 18:55
opps it was a small mistake got it working! dont mind but can u explain whats happended here homie ? @MichaelBerkowski – RockFeller Dumbstep Jan 07 '15 at 18:57
What do you mean by "whats happened here"? Do you want to know how this works? It assigns a variable to each grouped `post_id` and increments it for every row. The `join` initializes the variables to 0. In the end, the outer query limits to only rows where the variable row numner was `<= 3` to provide your limit per group. – Michael Berkowski Jan 07 '15 at 19:03
Where will the `where` clause go @MichaelBerkowski `post_id IN(1,2,3)`? – RockFeller Dumbstep Jan 07 '15 at 19:07
1

@RockFellerDumbstep, you can have in the inner query before the order by. check here. http://www.sqlfiddle.com/#!2/e04817/3 – radar Jan 07 '15 at 19:09

SELECT 3 comments for each post

1 Answers1