1

This is the table (achievements_t):

id Primary int(11) AUTO_INCREMENT
timestamp current_timestamp()
user_id varchar(34)
level varchar(40)
score int(11)
replay mediumblob

Now for each level and a certain user_id, I would like two numbers, one the placement, two total entries.

"LevelX you are in place 12 of 54"

How can I achieve that?

Ronnie
  • 126
  • 8
  • Does this answer your question? [MySQL - Get row number on select](https://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select) – Nico Haase Jan 21 '21 at 08:24

1 Answers1

1
WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY level ORDER BY score desc) rn
    FROM achievements_t)
)
SELECT cte.user_id,cte.level,cte.rn as userPlace,sum(t.score) as totalEntry
FROM cte cte inner join achievements_t t
WHERE 
t.user_id=cte.user_id
group by user_id,level
yusuf hayırsever
  • 624
  • 6
  • 17
  • With this code, you will see the rank and total entry for each user and each level. If do you want to use it for example in C#, you may only enter user_id or level, either two of them for a parameter and you will see the result. – yusuf hayırsever Jan 21 '21 at 08:50