I just can not implement such a request: you must output the id of the user who has the maximum number of friends.
There is such a table:

user_one user_two type 5 6 1 6 5 1 12 6 1 7 34 1 34 7 1 12 7 2 34 7 2

When I do this, everything is OK:

 SELECT COUNT(*) AS counted FROM `friends` WHERE `type`='1' AND `user_two`='6' OR `type`='1' AND `user_one`='6'

But I want to make sure that all records for individual users are counted and their maximum number is displayed. That is, so that I get the user id (it can be user_one and user_two , that is, with these fields, the difference is that they show who sent the request) whose type=1 . I hope clearly explained, thanks in advance!

  • one
    Well, sort by descending quantity and take the first record, delov something ... - Akina
  • Well, this is understandable, but how to do it? I tried this: SELECT MAX (counted) FROM (SELECT COUNT (*) AS counted FROM friends WHERE type = '1' GROUP BY user_one ) as counts But it doesn't work - paskalnikita
  • one
    7 user how many friends with type = 1? That's right - 2. then you need to count the records separately where it is in the first field and where in the second. then we need to count each entry in the table 2 times, first for one, then for another. so something like select user,count(1) from (select user_one as user from tab union all select user_two from tab) X order by count(1) desc limit 1 - Mike
  • Well, your request is wrong, it turns out that I am not moving beyond the first line - paskalnikita
  • 2
    But you wanted the "maximum number", so he gave exactly one line with this very maximum. And no one said that someone should give you the right request. I indicated the direction. Modify the query to your needs. for example, remove limit 1 to give quantities for all users - Mike

1 answer 1

 select user_ -- , cnt from ( select user_, count(*) as cnt from ( select user_, user_two from( select user_one as user_, user_two from friends where type = 1 union all select user_two, user_one from friends where type = 1 ) as t group by user_, user_two) as t group by user_) as t order by cnt desc, user_ asc limit 1
  • I need to display the id of the user who has the maximum number of friends (type = 1 and the id itself can be either user_one or user_two). Is it clear? Just your query shows the number of all lines. And I bring it all up like this: $ biggest_funbase_username = mysql_query ("SELECT user, count (*) from = select user_one") or die (mysql_error ()); $ result = mysql_fetch_assoc ($ biggest_funbase_username); echo "User -". $ result ['user']; - paskalnikita
  • Good. What should be displayed for user "6"? According to your second request in the production will be displayed 3. Is this correct? It seems to me that no. Because 6 has only 2 friends: 5 and 12. - jayrumi
  • Yes, that's right, there must be two friends. And it should output the id of the user who has the most friends (6 or 7 because they have the same number of friends). In case the user 6 had 3 friends, only the user id number 6 would be displayed. I also tried so, but it gives an error: SELECT MAX (counted) FROM (SELECT COUNT (*) AS counted FROM friends GROUP BY user_one , user_two union all) as counts - paskalnikita
  • Let's take a closer look. In the case when the 6 and 7 users have the maximum number of friends that need to be displayed: both of them or the first from the list, say, sorted by ID? - jayrumi
  • And yet, when counting friends, we do not pay attention to type? - jayrumi