How to GROUP BY and LIMIT N results from one field?

Question

In MySQL I have articles. Each article has category. I want get last N articles from each category ordered by date. How to do that in SQL? Also interested how to convert it to Yii2 Active Query.

Example: 10 articles, 3 categories. Category A has 2 articles, category B has 3 articles, category C has 5 articles.

With query like

SELECT *
FROM articles
GROUP BY 'category'
LIMIT 2
ORDER BY 'date'

I have to get 2 articles from A, 2 last articles from B and 2 last articles from C

Answer 1

One method is:

select a.*
from articles a
where a.ctime >= coalesce((select a2.ctime
                           from articles a2
                           where a2.category = a.category
                           order by a2.ctime desc
                           limit 1, 1
                          ), a.ctime
                         );

MysQL does not allow in with correlated subqueries with limit. Not sure why, but it does work for comparison operators.

Answer 2

Use subqueries. The nested SELECT statement will fetch article ids grouped by a1 articles category, ordered by date and limited to 2.

SELECT * FROM articles a1
WHERE a1.id IN 
  (SELECT a2.id FROM articles a2 
   WHERE a1.category = a2.category 
   ORDER BY ctime desc LIMIT 2)
ORDER BY a1.category, a1.ctime;

Note: this code won't work on older versions of MySQL. It will work on Oracle though.