+ Reply to Thread
Results 1 to 2 of 2

Thread: Get User ranking

  1. #1
    Junior Member
    Join Date
    Jan 2012
    Posts
    35

    Get User ranking

    I have a table called "users" with the column "id". I also have a table called "points" with the columns "user_id" and "amount".

    I need:

    1.) Users with duplicate sum of points to have the same rank

    Points Table
    Code:
    user_id     amount
       1          10
       2          20
       1           5
       3          20
       3          -5
       4          5
    Rank should be
    Code:
    rank     user_id    total
     1          2        20
     2          1        15
     2          3        15
     3          4        5
    2.) Needs to maintain the ranking from one page to another so the rank has to be gathered in the query and not the resulting PHP.

    3.) Display ALL users not just ones with rows in the points table because some users have 0 points and I want to display them last.

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    16
    Code:
    SELECT COUNT(*) AS rank
         , t.user_id
         , t.total 
    FROM
          ( SELECT   user_id
                 ,   SUM(amount) AS total
            FROM     points 
            GROUP BY user_id
          ) AS t
      JOIN
          ( SELECT DISTINCT
                     SUM(amount) AS total
            FROM     points 
            GROUP BY user_id
          ) AS dt
        ON 
            t.total <= dt.total
    GROUP BY t.user_id
    ORDER BY rank
           , user_id
    But the above may be really slow with a big table and points awarded often. It might be really better to have just this and calculate the ranks in your application code:
    Code:
    SELECT   users.id    AS user_id
         ,   SUM(amount) AS total
    FROM     
             users
      LEFT JOIN
             points
        ON   points.user_id = users.id
    GROUP BY users.id
    ORDER BY total DESC
           , user_id

+ Reply to Thread

Bookmarks

Posting Permissions

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