How to get MySql user position?

Question

MySQL

I use the following code to generate a list of the most active users:

SELECT user_id, COUNT(*) as user_commands FROM `log` GROUP BY user_id ORDER BY user_commands DESC

For example, I know a specific user_id - how can I find out its position in this list?

Answer 1

In general, you can do this:

SET @number := 0;
SELECT
  rating.*
FROM
  (
    SELECT
      (@number := @number + 1) AS number,
      user_id,
      COUNT(*) AS user_commands
    FROM
      `log`
    GROUP BY
      user_id
    ORDER BY
      user_commands DESC
  ) AS rating
WHERE
  rating.user_id = 2

But you should understand that such queries are not rational, and they are not suitable for large loads / data, so I would recommend making a separate table that aggregates user ratings