x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 259

# Calculate a user's position in a leader board

Hello

I have designed a league table which calculates points based on how many virtual cups of tea are given and received. 1 point for a given cup, 3 for a received.

A variable @I counts the position that the user ranks in the league, and it gives a Top 10.

How would I go about retrieving a given users position in the league, assuming most users will be outside the top ten? If a WHERE clause is used, the @I counter does not get to count the correct position in the league, and simply shows 1, as 1 record is returned.

Is a self-join needed...somehow?

Thanks!
``````+---------+---------------+-------------+---------------------+--------------+
| GIVE_ID | GIVE_UID_FROM | GIVE_UID_TO | GIVE_DATETIME       | GIVE_MESSAGE |
+---------+---------------+-------------+---------------------+--------------+
|       2 |     507426179 |   502129134 | 2008-05-02 17:16:44 | NULL         |
|       3 |     508580684 |   507426179 | 2008-05-02 17:21:31 | NULL         |
|       4 |     507426179 |   835984465 | 2008-05-02 17:23:39 | NULL         |
|       5 |     502129134 |   507426179 | 2008-05-02 17:23:45 | NULL         |
|       6 |     507426179 |   285100389 | 2008-05-02 17:23:46 | NULL         |
|       7 |     502129134 |   507426179 | 2008-05-02 17:24:56 | NULL         |
|       8 |     507426179 |   726170211 | 2008-05-02 17:29:14 | NULL         |
|       9 |     507426179 |   514816730 | 2008-05-02 17:29:54 | NULL         |
|      10 |     507426179 |   285100389 | 2008-05-02 17:47:33 | NULL         |
|      11 |     507426179 |   835984465 | 2008-05-02 18:00:00 | NULL         |
+---------+---------------+-------------+---------------------+--------------+

SET @I := 0;

SELECT `GIVE_UID_FROM` ,

(
SELECT COUNT( * )
FROM GIVE AS T
WHERE T.GIVE_UID_TO = SQ.GIVE_UID_FROM
)  	AS GIVE_TOTAL_RX,

COUNT( `GIVE_ID` ) AS GIVE_TOTAL_TX,

(
SELECT (GIVE_TOTAL_RX *3) + COUNT( `GIVE_ID` )
)  	AS GIVE_TOTAL_POINTS,

@I := @I + 1 AS `GIVE_POSITION`

FROM GIVE AS SQ
GROUP BY `GIVE_UID_FROM`
ORDER BY `GIVE_TOTAL_POINTS` DESC, `GIVE_TOTAL_RX` DESC
LIMIT 10
``````
0
matthewhamer
1 Solution

Author Commented:
I figured it out: Use a temporary table to store the results. Then query the temporary table.

Simply proceed the Select query with the following to generate an in-memory table which will be automatically destroyed when the connections drops. Perfect.
``````CREATE TEMPORARY TABLE LEAGUE ENGINE="MEMORY"
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.