CODEWITHSUNDEEP
mysqlsqlsortingranking
Alex K.
Alex K.

Reputation: 21

Sorting highest value of another query

to get the statistic value of a player in the last month I use this query:

SELECT COUNT(*) FROM test WHERE player='player1' AND statistic=0 AND time > DATE_SUB(now(), INTERVAL 1 MONTH)

Now I want the Top 10 players with the highest value.
Example:
player1 has the value 40 (from the query above)
player2 has the value 78 (from the query above)
player3 has the value 21 (from the query above)

Now the ranking must be:

  1. player2
  2. player1
  3. player3

But how can I sort the ranking? Because I need the value of the query above?
Thanks

Upvotes: 0

Views: 23

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269733

Is this what you want?

SELECT (@rn := @rn + 1) as ranking, p.*
FROM (SELECT player, COUNT(*) as cnt
      FROM test
      WHERE statistic=0 AND time > DATE_SUB(now(), INTERVAL 1 MONTH)
      GROUP BY player
      ORDER BY COUNT(*) desc
     ) p CROSS JOIN
     (SELECT @rn := 0) params
LIMIT 10;

Upvotes: 1

Related Questions