mySQL --> LIMIT selected rows, WHERE column value is same

Question

This is my query... SELECT * FROM comments WHERE content_id in (525, 537) LIMIT 60 This is the SS of result:

here content_id = 537 is selected 5 times. (comment_id is UNIQUE key )..

My question is: How to limit selected rows by 2, where values of content_id is same... Maximum two duplicate records for each content_id... like in this picture:

This may help you: https://stackoverflow.com/a/1442867/20860 I previously voted to close your question as a duplicate, but I chose the wrong duplicate. This answer I link to is closer to your question. — Bill Karwin, Jan 14 '20 at 18:52

score 0 · Answer 1 · answered Jan 14 '20 at 17:23

If you are running MySQL 8.0, you can do this with row_number():

select comment_id, content_id
from (
    select t.*, row_number() over(partition by content_id order by comment_id) rn
    from mytable t
) t
where rn <= 2

In earlier versions, one solution is a correlated subquery:

select t.*
from mytable t
where (
    select count(*) 
    from mytable t1 
    where t1.content_id = t.content_id and t1.comment_id < t.comment_id
) < 2

mySQL --> LIMIT selected rows, WHERE column value is same

1 Answers1