MySQL select statement - How to calculate the current ranking

Question

I have a table called user_rankings where votes (voted) are stored for each user. I want to display the current ranking of users (this week) that depends on how much votes the user got.

example to clarify:

RANK-NR, USERNAME, VOTED,
1, name1, 18 times
2, name1, 16 times
(my ranking here), myname, 13 times

In this example my ranking should be 3. If I'd have 17 votes, I would be number 2. If there would be five users above me, I would be number 8. I guess you get the point.

Now I can display the ranking number easily with an incrementing $i in PHP. But I only want to show a list limited to ten users (a top ten list) and directly after that my current ranking, if I'm not already in that top ten list. So I'm just wondering how to get my exact ranking number using MySQL. I'm assuming to have hundreds of users in this list with a different amount of votes.

This is my statement at the moment:

SELECT 
`voted`
FROM `users_ranking` 
WHERE 
`uid`='".$_SESSION['uid']."' 
AND 
WEEKOFYEAR(`date`)=WEEKOFYEAR(NOW()) 
LIMIT 1

possible duplicate of [Find the ranking of an integer in mysql](http://stackoverflow.com/questions/14294542/find-the-ranking-of-an-integer-in-mysql) — Aziz Shaikh, Dec 10 '14 at 06:32

score 1 · Answer 1 · answered Dec 10 '14 at 06:39

I can't give you the exact code, but i think the following can give you some idea

select 'RANK-NR', 'USERNAME', 'VOTED' from ( select 'RANK-NR', 'USERNAME', 'VOTED', rank() over (order by 'voted' desc) as rank from users_ranking where uid='".$_SESSION['uid']."' AND WEEKOFYEAR(date)=WEEKOFYEAR(NOW()) ) as abc where rank<11

i think rank() over (order by<>) should work

score 1 · Accepted Answer · answered Dec 10 '14 at 07:58

1

I just found out myself that this solution works:

SELECT *
FROM 
(
  SELECT  @ranking:= @ranking + 1 rank,
          a.`uid`
  FROM    `users_ranking` a, (SELECT @ranking := 0) b
  ORDER BY a.`votes` DESC
) s
WHERE `uid`='".$_SESSION['uid']."' 
AND 
WEEKOFYEAR(`date`)=WEEKOFYEAR(NOW()) 
LIMIT 1

answered Dec 10 '14 at 07:58

AlexioVay

4,338
2
31
49

Strictly speaking I think you should get the records from users_ranking in order (from a sub query) and then add the ranking to that. This will force MySQL to get the votes in order before it calculates the ranking. – Kickstart Dec 10 '14 at 09:38
Can you post an example please? @Kickstart – AlexioVay Dec 10 '14 at 10:58

Kickstart · Answer 3 · 2014-12-15T12:03:26.637

OK, example to go with my comment. What you have will often work, but there is nothing to force MySQL to do the sort before it applies the ranking.

As such using an extra level of sub query would give you this (not tested). The inner sub query is getting all the user ids for the relevant week in the right order, while the next outer sub query applies the ranking to this ordered result set. The outer query just gets the single returned row you require.

SELECT c.rank, c.uid
FROM
(
    SELECT @ranking:= @ranking + 1 rank, a.uid
    FROM 
    (
        SELECT uid, votes
        FROM    `users_ranking`
        WHERE WEEKOFYEAR(`date`) = WEEKOFYEAR(NOW()) 
        ORDER BY votes DESC
    ) a,
    (SELECT @ranking := 0) b
) c
WHERE c.uid = '".$_SESSION['uid']."' 
LIMIT 1

Another possibility avoiding the sub query and also avoiding the need for a variable is to do a join. This is (mis)using HAVING to slim down the result to the single row you are interested in. Down side of this solution is that if multiple users have the same score they will each get the same ranking.

SELECT b.uid, COUNT(a.uid) 
FROM users_ranking a
LEFT OUTER JOIN users_ranking b
ON WEEKOFYEAR(a.`date`) = WEEKOFYEAR(b.`date`) 
AND a.votes >= b.votes
GROUP BY b.uid
HAVING b.uid = '".$_SESSION['uid']."'

EDIT

To give the top 10 rankings:-

SELECT b.uid, COUNT(a.uid) AS rank
FROM users_ranking a
LEFT OUTER JOIN users_ranking b
ON WEEKOFYEAR(a.`date`) = WEEKOFYEAR(b.`date`) 
AND a.votes >= b.votes
GROUP BY b.uid
ORDER BY rank
LIMIT 10

Although in this case it might be quicker to use a sub query. You could then put the LIMIT clause in the sub query with the ORDER BY, hence it would only need to use the variables to add a rank to 10 rows.

I am not sure how to combine that with the query for a single user, mainly as I am not sure how you want to merge the 2 results together.

Thank you for your detailed reply. But can you please explain your solution a little bit more before I mark here a solution as correct? Is this about performance issues? What exactly happens in difference to my MySQL-statement? Also there can't be two users with the same amount of votes as I'm validating that this isn't allowed in my system. — AlexioVay, Dec 11 '14 at 14:23
The first solution forces the order before adding the ranking. This is a simple upgrade to the solution you have yourself. The down side of this solution (and yours) is that it uses a sub query and these tend to be poor for performance (as MySQL generally doesn't carry indexes through from a sub query), plus it forces the inner queries to bring back all the records with the rank applied. My 2nd solution avoids the sub query(s) and should use indexes (it might be more efficient to use an INNER JOIN rather than the LEFT OUTER JOIN). — Kickstart, Dec 11 '14 at 14:59
I'm not familiar with subqueries, so I didn't know about the poor performance until yet. I read that now on many websites. So I'd like to use the join solution. But there is the problem that your 2nd solution is assigned to a specific user. And above that select statement I have the Top10-list of all users. So there are two queries. Could they be shortened to one query somehow to have the top10 list and also this specific logged in user? This is my code: http://pastebin.com/xY00gG4g — AlexioVay, Dec 12 '14 at 17:31
Added a suggestion for that, but unsure about merging the 2 queries together. — Kickstart, Dec 15 '14 at 12:03

MySQL select statement - How to calculate the current ranking

3 Answers3

Linked