-1

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

DrDoom
  • 149
  • 3
  • 16

1 Answers1

1

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;
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Just tested it, and it seems to be sorting by playerId now instead of score/energyLeft, also tried by having score in the find_in_set and playerid in group_concat, but then all my positions were 0. same if i invert score/playerid. so im assuming i need to have both the same column in find in set and group concat. – DrDoom Mar 29 '22 at 20:52
  • @DrDoom `GROUP_CONCAT(player_id ORDER BY score ASC, energyLeft DESC)` returns all the player_ids sorted by score ASC, energyLeft DESC. You are doing something wrong. Post sample data. – forpas Mar 29 '22 at 20:59
  • Just tested again, and it seems to work. but i have 2 rows at position 0 that doesnt make sense, but if i ignore those. everything else is fine. PlayerId Score energyleft position Test10 100 1 0 Test2 90 25 0 Test3 6 20 1 Test4 9 16 2 Test5 13 12 3 Test6 18 10 4 Test18 19 5 5 Test11 22 100 6 Test111 22 90 7 – DrDoom Mar 30 '22 at 16:12
  • so i think ill just use your way and ignore position 0 – DrDoom Mar 30 '22 at 16:14
  • @DrDoom you should not get 0 as position. This is wrong. FIND_IN_SET() returns 0 only if the id is not there in the list. Also, if you want highest scores ranked first why do you use ORDER BY score ASC inside GROUP_CONCAT()? It should be ORDER BY score DESC. Check this: https://www.db-fiddle.com/f/nPctBdTP6HqZ5he9BgtMyJ/0 – forpas Mar 30 '22 at 16:18
  • for the score, i use ASC and DESC depending on the event, some event use time (so we need smallest value) and other use points(so we need biggest value) . and yeah its super weird to get position 0, but i was not familiar with find_in_set, found it online while searching – DrDoom Mar 30 '22 at 17:42
  • I guess i could explain what im trying to do instead. Im doing a leaderboard where you need to see the top 10 players, and your position and the +/- 5 position around you. so I could get the players that are position 1-10 and (x-10 to x+10) (x being the player position) – DrDoom Mar 30 '22 at 17:44
  • I added Having position > 0 to my query to remove the rows at position 0, hoping this will do, but not confident about it haha – DrDoom Mar 30 '22 at 17:44
  • @DrDoom what you mention in your last comments have nothing to do with the requirement as you describe it in your question. – forpas Mar 30 '22 at 17:51