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:
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
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
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
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.