0

With the following MySQL table:

+---------+-----------+
| user_id | user_name |
+---------+-----------+
|    1    |   user 1  |
|    2    |   user 2  |
|    3    |   user 3  |
|    4    |   user 4  |
|    5    |   user 5  |
+---------+-----------+
+---------+-------------+
| user_id | user_staff  |
+---------+-------------+
|    1    |  xxxxxx     |
|    1    |  xxxxxx     |
|    2    |  xxxxxx     |
|    2    |  xxxxxx     |
|    2    |  xxxxxx     |
|    3    |  xxxxxx     |
|    3    |  xxxxxx     |
|    4    |  xxxxxx     |
+---------+-------------+

This Query get me the Ranking table:

SELECT usr.user_name, COUNT(stf.user_id) AS score 
  FROM table_stafs AS stf 
    LEFT JOIN table_users AS usr ON usr.user_id=stf.user_id 
  GROUP BY usr.id 
  ORDER BY score DESC

How can get the position of a single user?

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
Phoxer
  • 413
  • 1
  • 4
  • 15

1 Answers1

0

You can add an auto increment column in the query result if you want ranks, e.g.:

SELECT usr.user_id,COUNT(stf.*) AS score, @r := @r + 1 AS rank
FROM table_stafs AS stf, (SELECT @r := 0) a 
LEFT JOIN table_users AS usr ON usr.user_id=stf.user_id 
GROUP BY usr.id ORDER BY score DESC
Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
  • If you believe this is the answer, suggest the existing Q&A material on the website (via a comment and a close vote). – hakre Jun 17 '17 at 22:27
  • this gave me the count of all rows in the table_staff not the count of a single user by user_id :/ – Phoxer Jun 17 '17 at 22:47