Very suddenly there was a problem with a lack of knowledge in sql (mysql). There is no time to replenish them. There is a task to make a rating by the number of open cases, by the user (roulette site). Each case opening is stored in the history table and has the key of the user who opened the case. In the user table, each user is also assigned a unique key (something like id). I can easily get the COUNT () of open cases. BUT! How do I get top (10,20,30, ...) users for open cases using mysql tools? You can also send to the necessary documentation on the topic.

  • And what is the top of open cases? quantity for each user? then just group by user id - Mike
  • And the table with user names, if they are needed, join'init after grouping to the subquery - Mike
  • one
    How do I get top (10,20,30, ...) users for open cases using mysql tools? Use the correct result sorting. And LIMIT to limit the number of entries. - Akina
  • @Mike can tell how join'init after grouping. After grouping, it gives an error, and if before ... krch I have been trying to figure it out for an hour, I still can’t give up = ( - Pancake
  • select ... from (select user_id, count(1) as cnt from tab group by user_id) x join users u on u.id=x.user_id - Mike select ... from (select user_id, count(1) as cnt from tab group by user_id) x join users u on u.id=x.user_id

1 answer 1

Here is the request I need.

 "SELECT login, cnt FROM (SELECT key_valid, COUNT(*) AS cnt FROM history_case GROUP BY key_valid) AS x JOIN user AS u ON (u.key_valid = x.key_valid) ORDER BY cnt DESC LIMIT $limit"

enter image description here