Rank rows from table and save ranking on another table with date

user_2002230 0
Viewed 33

I have a table with game scores: user_id, user_name, game_score

I would like to rank the users by game_score and then save the rank of each user in the ranking table with today's date.

I can select all the rows from the table:

SELECT game_score, user_id, user_name FROM scores ORDER BY game_score DESC

And then run a query to save each user to the ranking table

INSERT INTO ranking_table (user_id, user_rank, today) VALUES ($user_id, $user_rank, $today)

But, if I have 500 players, that's 500 queries.

Is there a way to make it the process with fewer queries?

mysql php
1 Answers
INSERT INTO ranking_table (user_id, user_rank, today)
WITH cte AS (SELECT user_id, 
                    RANK() OVER (ORDER BY game_score DESC) user_rank
             FROM scores)
SELECT user_id, 
       user_rank, 
       CURRENT_DATE
FROM cte;
user_101387340
Related