1

I have a users table and a scores table:

-------- users
Name                    |   Type
------------------------|----------------
uuid                    |   VARCHAR(36)
name                    |   VARCHAR(255)
...                     |   ...

-------- scores
Name                    |   Type
------------------------|----------------
uuid                    |   VARCHAR(36)
user_uuid               |   VARCHAR(36)
score                   |   INT(11)

I can fetch a user, including their total score using a subquery like this:

SELECT user.uuid, user.name,
(SELECT SUM(score) FROM scores WHERE user_uuid = user.uuid) AS score
FROM user WHERE user.uuid = [USER_UUID];

But now, how can I fetch the user's rank? That is, rank being determined by their score vs the scores of every other user.

Is it really necessary to loop through every single user, calculate all of their scores, and then order their scores to determine the rank of one single user? Performing this query on the fly seems taxing, especially if I have a large number of users. Should I instead build a separate rankings table, and re-sort the table after each INSERT into the scores table? That doesn't seem ideal either.

I have another application which will require on-the-fly ranking as well, but the calculations are much more severe: a user's score is determined by a complex algorithm spanning at least 5 different tables. So I really need some insight on database ranking in general - although the above problem is an easy way to represent it. What are some good practices to consider?

Hundley
  • 3,167
  • 3
  • 23
  • 45
  • Please show some sample input and expected output. – 1000111 Apr 27 '16 at 02:59
  • you can try this: row_number:=0; SELECT user_view.user_rank, user_view.uuid, user_view.name (SELECT (row_number:=row_number + 1) as user_rank, user.uuid, user.name, (SELECT SUM(score) FROM scores WHERE user_uuid = user.uuid) AS score FROM user, (SELECT @row_number:=0) AS t order by score) user_view; make sure to define row_number as number. – Dr. Stitch Apr 27 '16 at 03:04

1 Answers1

2

I think keeping the rank of each user in a different table(using a procedure when inserting data to the scores table) would be better. So that you can get the rank straight away when you need.

refer the mark as correct answer in this link. It might help you. rank function in sql

Community
  • 1
  • 1
Bhugy
  • 711
  • 2
  • 8
  • 23