Selecting Row(s) Based Upon Derived Value Maximum

Question

I need to display the most followed individuals in a group of people.

SELECT * FROM User
JOIN(
SELECT DISTINCT f.followee_id, COUNT(*) as cnt
FROM Follow f
GROUP BY f.followee_id
ORDER BY cnt desc) derv_table
WHERE User.id = derv_table.followee_id

Results in this table

  id  |             email             |  zipcode   | followee_id | cnt 
 -----|-------------------------------|------------|-------------|----- 
   80 | [email protected]            | 81629-3826 |          80 |   2 
   39 | [email protected] | 90222-0327 |          39 |   2 
    4 | [email protected]       | 35465-6959 |           4 |   2 
  100 | [email protected]    | 80558-1775 |         100 |   2 
   11 | [email protected]     | 06562-5156 |          11 |   1 
   49 | [email protected]       | 69874-3485 |          49 |   1 
   78 | [email protected]  | 73719-7076 |          78 |   1 
   13 | [email protected]         | 16426-2360 |          13 |   1

So I've gotten as far as ordering which people have the most followers, since followee_id and id are the same key.

This table continues on, the CNT is a derived (or calculated value), how do I only display the rows which contains the maximum of CNT (for N records that have the maximum, so LIMIT will not suffice) I've tried all sorts of joins and different conditions and haven't gotten anywhere.

Answer 1

In MySQL versions earlier than 8+, we can use a subquery:

SELECT * FROM User u
INNER JOIN
(
    SELECT f.followee_id, COUNT(*) AS cnt
    FROM Follow f
    GROUP BY f.followee_id
) t
ON u.id = t.followee_id
WHERE
    t.cnt = (SELECT COUNT(*) FROM Follow
             GROUP BY followee_id ORDER BY COUNT(*) DESC LIMIT 1);

In MySQL 8+ or later, we can take advantage of the RANK analytic function:

WITH cte AS (
    SELECT *,
        RANK() OVER (ORDER BY t.cnt DESC) rnk
    FROM User u
    INNER JOIN
    (
        SELECT f.followee_id, COUNT(*) AS cnt
        FROM Follow f
        GROUP BY f.followee_id
    ) t
    ON u.id = t.followee_id
)

SELECT *
FROM cte
WHERE rnk = 1;