Improve company productivity with a Business Account.Sign Up

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

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

Open in new window

0
matthewhamer
Asked:
matthewhamer
1 Solution
 
matthewhamerAuthor 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"

Open in new window

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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now