Add second column to order by mysql in a group

I have this query I made based on someone else question here. SELECT *, FIND_IN_SET( score, ( SELECT GROUP_CONCAT( score ORDER BY score ASC) FROM EventPlayerResult WHERE eventId = 'EventTest0') ) AS position FROM EventPlayerResult WHERE eventId = 'EventTest0' ORDER BY position ASC LIMIT 10

It gives me a leaderboard for the top 10 players. But when I run it, if 2 players has the same score, I need it to filter by another column (energyLeft). So I tried to add , energyLeft DESC inside of my GROUP_CCONCAT but it doesnt change anything. im not familiar with group concat and find in set. So where should I add the logic to order by energyLeft after ordering by score.

I tried something like this : SELECT *, FIND_IN_SET( score, ( SELECT GROUP_CONCAT( score ORDER BY score ASC, energyLeft DESC) FROM EventPlayerResult WHERE eventId = 'EventTest0') ) AS position FROM EventPlayerResult WHERE eventId = 'EventTest0' ORDER BY position ASC LIMIT 10

CodePudding user response：

You should use the player and not the score inside GROUP_CONCAT() so that the players are ranked by score first and then by energyLeft.

Assuming there is a column like player_id in the table:

SELECT *, 
       FIND_IN_SET( 
         player_id, 
         ( 
           SELECT GROUP_CONCAT(player_id ORDER BY score ASC, energyLeft DESC) FROM EventPlayerResult WHERE eventId = 'EventTest0'
         ) 
       ) AS position 
FROM EventPlayerResult 
WHERE eventId = 'EventTest0' 
ORDER BY position ASC LIMIT 10;