0

I'm using MySQL and I have a table that holds player scores, with a single player being able to log multiple scores. I'm trying to find the best way to rank the players using the MySQL ORDER BY keyword.

Right now what I'm doing is:

SELECT DISTINCT t.userName FROM (SELECT userName, score FROM scoreTable ORDER BY score DESC) t;

To use this for ranking it I'm assuming that the order from the sub-query is maintained by the main query.

Is this assumption correct? All my testing so far maintains the order properly. Is there a situation where it will not work?

Tim Dearborn
  • 1,178
  • 7
  • 18
Russbear
  • 1,261
  • 1
  • 11
  • 22
  • i think there will be no issue. One more thing if you use query "SELECT DISTINCT(userName) FROM scoreTable ORDER BY score DESC" then there will be no issue.. Why you are using subquery in above – Fathah Rehman P Nov 11 '12 at 06:11
  • If I use your suggested query it appears to take the first instance of each userName and uses that score to do the ordered ranking. – Russbear Nov 11 '12 at 06:36

2 Answers2

2

Would the below work for you?

SELECT userName, MAX(score) AS max_score 
FROM scoreTable GROUP BY username 
ORDER BY max_score DESC
Tim Dearborn
  • 1,178
  • 7
  • 18
1

I don't believe this is guaranteed behavior, but generally yes. It may be better to have the ORDER BY on the outer query if you want the final results ordered that way.

Indigenuity
  • 9,332
  • 6
  • 39
  • 68