select last three commenter for each post (Greatest n per group)

Question

MySQL

SELECT DISTINCT comments.commenter_id FROM comments WHERE ((oid IN (421,425) 
AND otype = 'post') (oid IN (331) AND otype = 'photo')) ORDER BY  
post_id,type,comment_id LIMIT 3

What i wanted to do is select last three distinct commenters for each individual post or photo with respective ids. i.e max 3 commenters for each o_id and o_type combinations

But the above instead of yielding me last three distinct commenters yields me total three. Where i am going wrong ? can anyone help me out ?

IF LIMIT IS 2

ID | oid | otype | commenter_id
1    1      post    1
2    1      post    1
3    1      post    2
4    1      post    3
5    2      post    1
6    1      photo   2
7    2      post    3

OUTPUT SHOULD BE

commenter_id| o_type | o_id
 3            post      1   
 2            post      1
 3            post      2
 1            post      2
 2            photo     1

score 1 · Answer 1 · edited May 23 '17 at 12:12

SOLVED - GREATESET N PER GROUP

That was easy though! :P

This question helped me lot.

SELECT DISTINCT t.commenter_id,t.o_id,t.otype
from 
(
SELECT c.*,
    @row_number:=if(@post_id = oid, @row_number + 1, 1) AS row_number,
       @oid:=oid AS varval
FROM comments c
join (select @row_number := 0, @oid:= NULL) as var
    ON 
    ((oid IN (425) AND otype = 'post') OR (oid IN (331) AND otype = 'photo'))

order by comment_id DESC
) t
where t.row_number <=2

select last three commenter for each post (Greatest n per group)

1 Answers1

SOLVED - GREATESET N PER GROUP