mysql select inner join with limit

Question

I have 2 tables, category and articles, I want to get 5 articles for each category, the tables look like this:

category: id, name
articles: id, title, body, category_id

So what I normally do is INNER JOIN, but I get all rows, how can I specify that I need only 5 rows per category, I imagine it would need a SELECT within a SELECT ?

Answer 1

You can use a rank query mysql does not have window functions for this type of results to get n records per group,i will not suggest a group_concat solution because as articles terms says that there can be enough data and easily by pass the 1024 character limit constraint if you increase this limit it also has a dependency on max_allowed_packet too

SELECT * FROM (
SELECT *,
@r:= CASE WHEN @g = c.id THEN @r +1 ELSE 1 END rownum,
@g:= c.id catgroup
 FROM category c
 JOIN articles a ON (c.id = a,category_id)
CROSS JOIN (SELECT @g:=0,@r:=0) t2
ORDER BY c.id , a.`date` desc
) t
 WHERE rownum <= 5

Above will rank each article within in its category group you can see the result of rownum alias and in outer query just filter the results of articles to 5 per category group

Answer 2

You can hack your query using group_concat in MySQL since MySQL does not support windows functions unfortunately. It won't look pretty though.

You can use this blog post as inspiration to build the query you need.

Other possibility is to retrieve every article in category and filter the top 5 yourself at the client side (PHP, Java, Python, you name it).