Agfdhyk

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 | kkiehn@example.com            | 81629-3826 |          80 |   2 

   39 | berenice.predovic@example.com | 90222-0327 |          39 |   2 

    4 | schaden.lea@example.com       | 35465-6959 |           4 |   2 

  100 | kathryne.braun@example.org    | 80558-1775 |         100 |   2 

   11 | auer.sterling@example.net     | 06562-5156 |          11 |   1 

   49 | arlie.ortiz@example.org       | 69874-3485 |          49 |   1 

   78 | beahan.andreanne@example.net  | 73719-7076 |          78 |   1 

   13 | kaitlyn28@example.com         | 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.

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;