querying for user's ranking in one-to-many tables

Question

I am trying to write a query to find the score rank of a user's games. I need it to take in a user id and then return that user's relative ranking to other user's scores. There is a user and a game table. The game table has a userId field with a one-to-many relationship.

Sample table:

users:
id freebee
1 10
2 13

games:
userId score
1 15
1 20
2 10
1 15

passing $id 1 into this function should return the value 1, as user 1 currently has the highest score. Likewise, user 2 would return 2.

Currently this is what I have:

SELECT outerU.id, (

SELECT COUNT( * )  
FROM users userI, games gameI  
WHERE userI.id = gameI.userId  
AND userO.id = gameO.userId  
AND (  
   userI.freebee + SUM(gameI.score)  
   ) >= ( userO.freebee + SUM(gameO.score) )  
) AS rank  
FROM users userO,  
games gameO  
WHERE id = $id

Which is giving me an "invalid use of group function" error. Any ideas?

Answer 1

I'm not a MySQL person, but I believe that the usual way to do ranking in it is using a variable within your SQL statement. Something like the below (untested):

SELECT
    SQ.user_id,
    @rank:=@rank + 1 AS rank
FROM
(
    SELECT
        U.user_id,
        U.freebee + SUM(COALESCE(G.score, 0)) AS total_score
    FROM
        Users U
    LEFT OUTER JOIN Games G ON
        G.user_id = U.user_id
) SQ
ORDER BY
    SQ.total_score DESC

You could use that as a subquery to get the rank for a single user, although performance-wise that might not be the best route.

Answer 2

The query does not like the reference of an outer table in the Sum function SUM(gameO.score) in the correlated subquery. Second, stop using the comma format for joins. Instead use the ANSI syntax of JOIN. For example, in your outer query did you really mean to use a cross join? That is how you wrote and how I represented it in the solution below but I doubt that is what you want.

EDIT

I've adjusted my query given your new information.

Select U.id, U.freebee, GameRanks.Score, GameRanks.Rank
From users As U
    Join    (
            Select G.userid, G.score
                , (
                    Select Count(*)
                    From Games As G2
                    Where G2.userid = G.userid
                        And G2.Score > G.Score
                    ) + 1 As Rank
            From Games As G
            ) As GameRanks
        On GameRanks.userid = U.id
Where U.id =1

Answer 3

Here is "simplified" version for calculating a rank based only on "games" table. For calculating rank for a specific game only you need to add additional joins.

SELECT COUNT(*) + 1 AS rank
FROM   (SELECT userid,
               SUM(score) AS total
        FROM   games
        GROUP  BY userid
        ORDER  BY total DESC) AS gamescore
WHERE  gamescore.total > (SELECT SUM(score)
                          FROM   games
                          WHERE  userid = 1)  

It's based on the idea that ranking == number of players with bigger score + 1

Answer 4

SELECT u.id,total_score,
 ( SELECT COUNT(*) FROM
    (SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score
     FROM users u1
     LEFT JOIN games g ON (g.userId = u1.id)
     GROUP BY u1.id
    )x1
   WHERE x1.total_score > x.total_score
 )+1 as rank,

( SELECT COUNT(DISTINCT total_score) FROM
    (SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score
     FROM users u1
     LEFT JOIN games g ON (g.userId_Id = u1.id)
     GROUP BY u1.id
    )x1
   WHERE x1.total_score > x.total_score
 )+1 as dns_rank

 FROM users u

 LEFT JOIN
  ( SELECT u1.id, (IFNULL(u1.freebee,0)+ IFNULL(SUM(score),0)) as total_score
    FROM users u1
    LEFT JOIN games g ON (g.userId = u1.id)
    GROUP BY u1.id
  )x ON (x.id = u.id)

rank - (normal rank - e.g. - 1,2,2,4,5), dns_rank - dense rank (1,2,2,3,4). Column total_score - just for debugging...

Answer 5

Check this out: http://rpbouman.blogspot.com/2009/09/mysql-another-ranking-trick.html