[Luximinus]

Hi, would like some help here.

SELECT p.author_name, Year(FROM_UNIXTIME(p.post_date)) as year, Month(FROM_UNIXTIME(p.post_date)) as month, COUNT(*) AS antall
FROM posts AS p, topics AS t
WHERE p.topic_id = t.tid AND t.forum_id = 236
GROUP BY year, month, p.author_name
ORDER BY year, month, antall desc

Can for example return the following:
author_name: year - month - antall
User1: 2006 - 11 - 138
User2: 2004 - 12 - 116
User3: 2004 - 12 - 102
User2: 2005 - 1 - 111
User1: 2005 - 1 - 97

But I want it to return like this:
User1: 2006 - 11 - 138
User2: 2004 - 12 - 116
User2: 2005 - 1 - 111

I only want one result per month in the specified year, and the result should be the user who had the biggest amount of posts in the category in the given month in the given year.

So I wonder if it is possible to extend the sql-query to do this, instead of me having to do it manually?


(The sql is used in the SQL toolbox in Invision Power Board 3.0)

[r937]

SELECT s1.author_name
     , s1.theyear
     , s1.themonth
     , s2.antmax
  FROM ( SELECT p1.author_name
              , Year(FROM_UNIXTIME(p1.post_date)) AS theyear
              , Month(FROM_UNIXTIME(p1.post_date)) AS themonth
              , COUNT(*) AS antall
           FROM topics AS t1
         INNER
           JOIN posts AS p1
             ON p1.topic_id = t1.tid
          WHERE t1.forum_id = 236
         GROUP
             BY p1.author_name
              , theyear
              , themonth ) AS AS s1
INNER
  JOIN ( SELECT Year(FROM_UNIXTIME(p2.post_date)) AS theyear
              , Month(FROM_UNIXTIME(p2.post_date)) AS themonth
              , COUNT(*) AS antmax
           FROM topics AS t2
         INNER
           JOIN posts AS p2
             ON p2.topic_id = t2.tid
          WHERE t2.forum_id = 236
         GROUP
             BY theyear
              , themonth ) AS s2
    ON s2.theyear  = s1.theyear
   AND s2.themonth = s1.themonth
   AND s2.antmax = s1.antall

[Luximinus]

I added this to the end..

ORDER BY antmax desc
LIMIT 5

...and it definitely didnt work. This is the result:
author_name: theyear - themonth - antmax
User1: 2006 -11 - 138
User2: 2006 - 11 - 138
User3: 2006 - 11 - 138
User4: 2006 - 11 - 138
User5: 2006 - 11 - 138


In addition when I didnt take limit 5 on, I got a very very long result page with lots of results. My browser Opera, even began lagging, and I had a hard time going away from the page. The worst thing is that it was around 2000+ other pages with the same.

Seems to me that your query adds every user that has ever posted in the category to every number. Because the month, year and antmax is correct, but it lists a lot of users with the same month, year and antmax.

But thanks for trying Hope you or anyone else is able to figure this out. If it is possible at all when just using sql.

[r937]

Originally Posted by Luximinus

...and it definitely didnt work.

aargh!!! you'e right

i've edited the query and highlighted the necessary changes

[Luximinus]

The only thing I changed was adding the database table prefix we use on the forum. I also removed an extra AS that was in the query. It caused the query to fail at first.
( GROUP BY p1.author_name, theyear, themonth ) AS AS s1 )

And I am very certain that 100+ members did not get exactly 138 posts in that category in november 2006..

If I run the first sub-query by itself and, as last time, adds; order by antall desc limit 5. I get this result:

author_name: theyear - themonth - antall
User1: 2006 - 11 - 138
User2: 2004 - 12 - 116
User3: 2004 - 12 - 102
User4: 2006 - 11 - 99
User1: 2005 - 1 - 97

[r937]

please see my previous post

[Luximinus]

Sorry for my late reply, I have been away. But that didnt work either. It just returns this:

author_name: theyear - themonth - antmax
user: 2010 - 7 - 1

It just displays one user from one month.