How to get the maximum number of duplicate entries for each user?

Question

PHP

There is a table
id desc some_id user_id
1 aaa 222 4
2 ddd 222 4
3 rrr 333 5
4 ggg 404 4
5 mmm 333 5
6 uuu 602 5
As a result of SELECT you need to get

user_id    some_id   summ 
   4          222       2
   5          333       2

Ie it is necessary to receive for each user where some_id repeats at the same time maximum.
Here is my SELECT

SELECT user_id, some_id COUNT(some_id) AS summ FROM table1 GROUP BY some_id  ORDER BY some_id DESC

I get the result

user_id     some_id   summ 
   4          222       2
   5          333       2
   4          404       1
   5         606        1

How to discard the last 2 records. There are a lot of users and entries in the table (More than 1 million)

Answer 1

If not optimized, then for example like this

SELECT user_id, some_id, COUNT(some_id) AS summ 
FROM table1 
GROUP BY some_id 
HAVING summ = (
  SELECT MAX(sum1) FROM (
    SELECT COUNT(some_id) AS sum1 FROM table1 GROUP BY some_id) 
  AS t1)

Answer 2

SELECT user_id, some_id, COUNT(some_id) AS summ FROM table GROUP BY user_id HAVING summ