Activetopics - Get max 5 topics per category
- by Arjen
Hey,
I want to get the 5 latest active topics within several category's. Each topic has a subcatid and this subcatid relates to a catid.
What I want is to get the 5 active topics within each catid.
I'm trying to use the query below, but this isn't working at all:
set @num := 0, @catid := 0;
SELECT
forum_posts.topicid,
forum_topics.titel,
forum_topics.sticky,
forum_topics.gesloten,
MAX(forum_cats.id) AS catid,
MAX(forum_cats.titel) AS cattitel,
MAX(forum_subcats.id) AS subcatid,
MAX(forum_posts.id) AS maxid,
DATE_FORMAT(MAX(forum_posts.datum), '%d-%m-%Y om %H:%i uur') AS datum,
UNIX_TIMESTAMP(MAX(forum_posts.datum)) AS laatstereactieunix,
(COUNT(forum_posts.id) - 1) AS reactieaantal,
@num := IF(@catid = MAX(forum_cats.id), @num + 1, 1) AS row_number,
@catid := MAX(forum_cats.id) AS dummy
FROM
forum_posts
INNER JOIN
forum_topics
ON
forum_topics.id = forum_posts.topicid
INNER JOIN
forum_subcats
ON
forum_subcats.id = forum_topics.subcat
INNER JOIN
forum_cats
ON
forum_cats.id = forum_subcats.cat
WHERE
forum_cats.id IN (1)
AND
forum_topics.gesloten != '1'
GROUP BY
forum_posts.topicid,
forum_topics.titel,
forum_topics.sticky,
forum_topics.gesloten
HAVING
row_number <= 5
ORDER BY
forum_cats.id ASC,
MAX(forum_posts.datum) DESC
When executing this code I get always the same number (1) for row_number, so this is not the result I want.
Does anyone know how I can get this work?
Thanks!
