0

I have a table "Friendship", which looks like this:

CREATE TABLE Friendship (
    User1 INT,
    User2 INT,
    PRIMARY KEY (User1, User2),
    CONSTRAINT columns_cannot_equal CHECK (User1 <> User2)
);

Here is some sample data:

user_id,friend_id
134443,5888
232309,8909
232309,185595
232309,172634
232309,118037
80195,46650
80195,80429
80195,216751
80195,224625
175987,175483
175987,243131
175987,141773
175987,226862
247382,68616
247382,183025
101421,91108
101421,159948
101421,12395
101421,55391
201263,104651
201263,175709
201263,243725
201263,141645
241625,266922
241625,184460
241625,103497
241625,16108
4860,175056
4860,103123
4860,184967

I want to find the user with the most friends.

I already wrote the following query, that counts the number of friends for each user, but I do not know how to get the maximum number of friends from that.

SELECT  Friendship.User1, COUNT(Friendship.User2)
FROM Friendship
GROUP BY Friendship.User1
UNION ALL
SELECT  Friendship.User2, COUNT(Friendship.User1)
FROM Friendship
GROUP BY Friendship.User2

Could you please help me?

3nondatur
  • 353
  • 2
  • 9

1 Answers1

1

You can run this query to find the user with most friends:

SELECT user_id, COUNT(*) AS friend_count
FROM (
    SELECT User1 AS user_id
    FROM Friendship
    UNION ALL
    SELECT User2 AS user_id
    FROM Friendship
) AS all_users
GROUP BY user_id
ORDER BY friend_count DESC
LIMIT 1;
Maninder
  • 1,539
  • 1
  • 10
  • 12