Mysql select inner join with constraint
I have 2 tables, categories 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 usually do is INNER JOIN, but I get all rows, how can I indicate that I only need 5 rows for each category, I think this would require a SELECT in a SELECT?
source to share
You can use a rank query. mysql does not have windowing functions for this result type to get n records per group, I will not suggest a solution group_concat
because in terms articles
it says there can be enough data and easily overcoming the 1024 chars limit limit if you increase this limit it also has dependence 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 evaluate each article within its category group, you can see the result of the alias rownum
, and in the outer query, just filter the results of articles by 5 in the category group
source to share
You can hack your query with group_concat
MySQL, as MySQL unfortunately doesn't support Windows features. It won't look pretty though.
You can use this blog post as inspiration to create the query you need.
Another possibility is to fetch each article in a category and filter the top 5 on the client side (PHP, Java, Python, you name it).
source to share