CODEWITHSUNDEEP
mysqlsqlrank
01btoniz
01btoniz

Reputation: 21

How to store rank of a user in a level with his score in SQL table

I'm pretty lost with all the SQL stuff, and I'd like to know how to implement a field that gives the player a rank depending of the level and the score he set.

What I currently have

What I want

I'm not that good with mySQL so that's why I'd like to know how should I do this.

Upvotes: 0

Views: 763

Answers (3)

Nikhil
Nikhil

Reputation: 3950

try this:

select level,user,score,
rank() over(partition by level order by score desc) as rank
from Tablename;

Upvotes: 0

forpas
forpas

Reputation: 164089

For MySql 8.0 you can use rank():

select 
  t.*, 
  rank() over (partition by t.level order by t.score DESC) `rank`
from tablename t
order by level, user

See the demo.
For earlier versions:

select 
  t.*,
(select count(*) from tablename where level = t.level and score > t.score) + 1 `rank`  
from tablename t

See the demo.
Results:

| level | user | score | rank |
| ----- | ---- | ----- | ---- |
| 1     | A    | 10    | 3    |
| 1     | B    | 15    | 2    |
| 1     | C    | 30    | 1    |
| 2     | A    | 20    | 2    |
| 2     | B    | 10    | 3    |
| 2     | C    | 40    | 1    |

Upvotes: 1

H.G.
H.G.

Reputation: 413

Calculated values are not something that you should be storing in the tables. Similar exmple is age. You do not store the age of the user, but its DOB. Values that keep changing are a complex issue when discussing database modeling.

There is a name for this type of a problem. It is called a slowly changing dimensions wiki-link. Therefore, I would advise you to implement any calculation logic in you application, rather then having it stored in the database.

But, if for some reason you must have it on your DB level, you can always create a procedure that gets executed at certain periods of the day. However, this might create situations that your data in the tables might not be accurate until the time that procedure gets executed introduction to procedures.

Upvotes: 1

Related Questions