CODEWITHSUNDEEP
mysqlmax
Yuda Prawira
Yuda Prawira

Reputation: 12471

How can I get maximum user referral with MySQL query?

I have created a new system that there can used with referral system, but how can I get the information about the user who has the largest number of referrals?

I imagine the SQL command is like this:

select * from user where refered_user=max(count(user.refered_user));

What is the correct notation?

Upvotes: 0

Views: 391

Answers (1)

Gus
Gus

Reputation: 7349

I presume that your user table looks something like:

TABLE: user
COLUMN: id int PK
COLUMN: refered_user int FK ON user.id

Edit: removed HAVING clause and added subquery

Without using HAVING to find the MAXimum number of referrals, I have added a new subquery to find this:

SELECT MAX(referals)
FROM (
  SELECT refered_user, COUNT(1) referals
  FROM user
  GROUP BY refered_user
)

Then, I have used this inside a query to get the users who have referred this many times:

SELECT refered_user, referals
FROM (
  SELECT refered_user, COUNT(1) referals
  FROM user
  WHERE refered_user IS NOT NULL
  GROUP BY refered_user
) AS ruser
WHERE referals = (
  SELECT MAX(referals)
  FROM (
    SELECT refered_user, COUNT(1) referals
    FROM user
    WHERE refered_user IS NOT NULL
    GROUP BY refered_user
  ) AS muser
)

Finally, I have used this as the JOIN query:

SELECT u.*
FROM user u
INNER JOIN (
 SELECT refered_user, referals
 FROM (
  SELECT refered_user, COUNT(1) referals
  FROM user
  WHERE refered_user IS NOT NULL
  GROUP BY refered_user
 ) AS ruser
 WHERE referals = (
  SELECT MAX(referals)
  FROM (
   SELECT refered_user, COUNT(1) referals
   FROM user
   WHERE refered_user IS NOT NULL
   GROUP BY refered_user
  ) AS muser
 )
) AS r ON (r.refered_user = u.id)

End Edit

Please note that as more than one user can have the same (maximum) number of referrals, then this query can return 0 (for an empty table), 1 or more rows.

Upvotes: 1

Related Questions