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 ?

Answer 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