How do I write this query?
This is best done by LATERAL JOINs or if it is not available window functions. MySQL unfortunately doesn't support either but the latter is relatively easy to be mimicked.

&gt; set @rownum=0;
&gt; set @grp_lag=null; 
&gt; select id, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id  
from message 
order by grp_id, created_at desc;

+-------+--------+------------------+
| id    | rownum | @grp_lag:=grp_id |
+-------+--------+------------------+
| 50008 |      1 |                1 |
| 50007 |      2 |                1 |
| 50001 |      3 |                1 |
| 50004 |      4 |                1 |
| 50002 |      1 |                2 |
| 50005 |      2 |                2 |
| 50003 |      1 |                3 |
| 50006 |      1 |                6 |
+-------+--------+------------------+
8 rows in set (0.00 sec)


After having this you can have the final query something like this:

-- Set initial values for mimicing the window function
set @rownum=0;
set @grp_lag=null; 

-- Query every message where rownum &lt; per group limit
select * from (
    -- subquery providing rownumber for every group order by the created_at column
    select *, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id 
    from message 
    where grp_id in (1,2)
    -- Futher filtering for optimisation if possible
    order by grp_id, created_at desc
) numbered
where rownum &lt; 50;


You can wrap this into a procedure to execute it easier. Please keep in mind, set calls also return and they need to be in the same session (yet another reason for a stored procedure).

&gt; set @rownum=0;
Query OK, 0 rows affected (0.00 sec)


The query above will fetch every row where grp_id matches the in condition and generate the row number for each per group. So in case of large number of messages further optimisations are necessary. For example you can heuristically filter by created_at > [estimated date for having at least 50 message].