Solved

# Calculate a user's position in a leader board

Posted on 2008-06-18
253 Views
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
Question by:matthewhamer
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
1 Comment

Accepted Solution

matthewhamer earned 0 total points
ID: 21818609
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Oracle Date 6 42
dat and idx extensions 11 43
Microsoft SQL Query sum values over time periods 3 27
SQL Server Reports (SSRS 2014) - Reports Timing Out 11 118
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
###### Suggested Courses
Course of the Month4 days, 3 hours left to enroll