Clash Royale CLAN TAG#URR8PPP

MySQL update table only with the highest values of another table

For a game site.

All games are recorded if the player's score is greater than his old score

Table of all players (over 10,000 players)

CREATE TABLE games (
PlayerID INT UNSIGNED,
Date TIMESTAMP(12),
Score BIGINT UNSIGNED DEFAULT 0,
#...other data
);


Once a month, I do an update of the table of records best. And after I erase all games.

best

games

Table of best players (top 50)

CREATE TABLE best (
#...same as games, without final other data
PlayerID INT UNSIGNED,
Date TIMESTAMP(12),
Score BIGINT UNSIGNED DEFAULT 0
);


So I add the 50 best players of the table games in to the table best:

games

best

INSERT INTO best (PlayerID, Date, Score)
SELECT PlayerID, Date, Score FROM games ORDER BY Score DESC LIMIT 50;


And after (and this is where I have a problem) I try to keep in best only the best 50. At this point best contains 100 lines.

best

best

What I have to do:

PlayerID

Score

->

+----------+---------+
| PlayerID | Score |
+----------+---------+
| 25 | 20000 | New
| 25 | 25000 | Old best
| 40 | 10000 | Old best
| 57 | 80000 | New best
| 57 | 45000 | Old
| 80 | 35000 | New best
+----------+---------+


I have to retain in the end only 50 lines (the ones with "best" in my example).

I tried many things, but I have not succeeded in achieve the expected result.

I am using PHP, so if it is possible to do it simply with a intermediare storage in an array, that's fine too.
The speed is not a priority because it is an operation that is done only once a month.

2 Answers
2

The following SQL returns the top 50 scores:

SELECT `PlayerId`, max(`Score`) MaxScore
FROM (
SELECT `PlayerId`, `Date`, `Score` FROM games
UNION
SELECT `PlayerId`, `Date`, `Score` FROM best
) t
GROUP BY `PlayerId`
ORDER BY `MaxScore` DESC
LIMIT 50


You can use the result to overwrite the table best. For this you also need the corresponding Date field, which is missing so far. The next SQL will also return a maxDate field which corresponds to the highscore.

best

Date

maxDate

SELECT t2.`PlayerId`, max(t2.`Date`) maxDate, top.`MaxScore`
FROM
(
SELECT `PlayerId`, max(`Score`) MaxScore
FROM (
SELECT `PlayerId`, `Date`, `Score` FROM games
UNION
SELECT `PlayerId`, `Date`, `Score` FROM best
) t1
GROUP BY `PlayerId`
ORDER BY `MaxScore` DESC
LIMIT 50
) top
LEFT JOIN (
SELECT `PlayerId`, `Date`, `Score` FROM games
UNION
SELECT `PlayerId`, `Date`, `Score` FROM best
) t2 ON t2.`PlayerId` = top.`PlayerId` AND t2.`Score` = top.`MaxScore`
GROUP BY t2.`PlayerId`
ORDER BY top.`MaxScore` DESC


To transfer the new top 50 highscores into the best table you can use a temporary table like tmp_best. Insert the top scores into the empty table tmp_best with (you have to insert your select query from above):

best

tmp_best

tmp_best

INSERT INTO tmp_best (`PlayerId`, `Date`, `Score`)
SELECT ...


After this the best table can be emptied and then you can copy the rows from tmp_best into best.

best

tmp_best

best

Here is an alternative solution, which has simplified SQL. The difference
to the solution above is the using of a temporary table tmp_all at the beginning for the unified data. Before using the following SQL you have to create tmp_all, which can be a copy of the structure of games or best.

tmp_all

tmp_all

games

best

DELETE FROM tmp_all;

INSERT INTO tmp_all
SELECT `PlayerId`, `Date`, `Score` FROM games
UNION
SELECT `PlayerId`, `Date`, `Score` FROM best
;

DELETE FROM best;

INSERT INTO best (`PlayerId`, `Date`, `Score`)
SELECT t2.`PlayerId`, max(t2.`Date`) maxDate, top.`MaxScore`
FROM
(
SELECT `PlayerId`, max(`Score`) MaxScore
FROM tmp_all t1
GROUP BY `PlayerId`
ORDER BY `MaxScore` DESC
LIMIT 50
) top
LEFT JOIN tmp_all t2 ON t2.`PlayerId` = top.`PlayerId` AND t2.`Score` = top.`MaxScore`
GROUP BY t2.`PlayerId`
ORDER BY top.`MaxScore` DESC
;


best

best

max()

best

games

SELECT PlayerID, Date, Score FROM games ORDER BY Score DESC LIMIT 50
UNION
SELECT PlayerID, Date, Score FROM best


Here you'll get the best 50 players all-time. Then, as suggested by @ethrbunny, erase the best table and populate it again with the above query. You can use a TEMPORARY TABLE

UNION guarantees you that you'll get no duplicated player

best

DROP TABLE

CREATE TABLE

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.