Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: SQL query to retrieve user with most points in forum category each month

    Hi, would like some help here.

    Code:
    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)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    Last edited by r937; 07-06-10 at 16:22.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    4
    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.
    Last edited by Luximinus; 07-06-10 at 16:02.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Luximinus View Post
    ...and it definitely didnt work.
    aargh!!! you'e right

    i've edited the query and highlighted the necessary changes
    Last edited by r937; 07-06-10 at 16:23.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2010
    Posts
    4
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please see my previous post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2010
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •