Select *, MAX(Rank) from () as x, only returns one row??
Im making a leaderboard, where I give players a rank. I want to return the current rank that a specific player has, but also the lowest rank possible (That would be the highest number ofc). The issue is that if I add "SELECT *, MAX(RANK) FROM", it will only return one row, with the max rank, yet I want the max rank to be displayed on all rows of all players.
My question is then how I would do this.
Here's my SQL code
SELECT *, MAX(RANK) FROM (
SELECT PlayerId,
SUM(Score) AS 'TScore',
SUM(Kills) AS 'TKills',
SUM(Deaths) AS 'TDeaths',
SUM(Hits) AS 'THits',
SUM(Shots) AS 'TShots',
SUM(Headshots) AS 'THeadshots',
SUM(Kills)/SUM(Deaths) as 'TKd',
SUM(Hits)/SUM(Shots)*100 as 'TAc',
SUM(Headshots)/SUM(Hits)*100 as 'TAcHs',
COUNT(MinigameType) AS 'TSessions',
(Row_Number() OVER w) AS 'Rank'
FROM UserSessionData
WHERE (ServerId LIKE 'eu-%' OR ServerId LIKE 'us-%' OR ServerId LIKE 'au-%') AND (START >= (DATE_ADD(date_add(LAST_DAY(CURDATE()), interval 1 DAY), INTERVAL -1 MONTH))) AND MinigameType='1'
GROUP BY PlayerId WINDOW w AS (ORDER BY TScore DESC, TKd DESC, TKills DESC, TDeaths)
ORDER BY TScore DESC, TKd DESC, TKills DESC, TDeaths
) AS X
Returns:
| PlayerId | TScore | TKills | TDeaths | THits | TShots | THeadshots | TKd | TAc | TAcHs | TSessions | Rank | MRank |
| ----------------: | -----: | ------: | ------: | ----: | -----: | ---------: | -----: | ------: | ------: | --------: | ---: | ----: |
| 76561198123584578 | 1694 | 752 | 132 | 4036 | 17046 | 391 | 5,6970 | 23,6771 | 9,6878 | 12 | 1 | 5 |
But I would like and though it should return something like this where the MRank is max on the rows:
| PlayerId | TScore | TKills | TDeaths | THits | TShots | THeadshots | TKd | TAc | TAcHs | TSessions | Rank | MRank |
| ----------------: | -----: | ------: | ------: | ----: | -----: | ---------: | -----: | ------: | ------: | --------: | ---: | ----: |
| 76561198123584578 | 1694 | 752 | 132 | 4036 | 17046 | 391 | 5,6970 | 23,6771 | 9,6878 | 12 | 1 | 5 |
| 76561198114065411 | 1166 | 508 | 102 | 3147 | 13144 | 212 | 4,9804 | 23,9425 | 6,7366 | 11 | 2 | 5 |
| 76561198102253642 | 1072 | 376 | 240 | 2878 | 15260 | 180 | 1,5667 | 18,8598 | 6,2543 | 11 | 3 | 5 |
| 76561198274960413 | 1004 | 337 | 113 | 1946 | 7837 | 147 | 2,9823 | 24,8309 | 7,5540 | 11 | 4 | 5 |
| 76561198355741964 | 990 | 390 | 151 | 2907 | 12925 | 158 | 2,5828 | 22,4913 | 5,4352 | 7 | 5 | 5 |
Hope someone will help me :)