mysql - Select most "popular" follower for particular person. The more followers someone has, the more "popular" they are -

find "popular" follower specific person. more followers has, more "popular" are.

i need sql query select popular follower of particular people.

my table - (followers)

id | person_id | follower_person_id 1    1            2 2    1            3 3    2            1 4    2            4 5    3            1 6    3            2 7    3            4 8    4            3 

for example person_id 1 has total 2 follower, person_id 2 has total 2 followers, person_id 3 has total 3 followers , person_id 4 has total 2 followers.

therefore, person_id 3 popular follower person_id 1, person_id 1 popular follower person_id 2 , on...

here query not working...

select follower_person_id followers f f.person_id = 1 group f.follower_person_id having max(select count(*) followers person_id = f.follower_person_id) 

you can use following query number of followers each person:

select person_id, count(*) cnt followers group person_id  

output:

person_id cnt ------------- 1         2 2         2 3         3 4         1 

using above query derived table can number of followers follower each person (sounds bit complicated!):

select t1.person_id, t1.follower_person_id, t2.cnt followers t1 join (    select person_id, count(*) cnt    followers    group person_id  ) t2  on t1.follower_person_id = t2.person_id 

output:

person_id, follower_person_id, cnt ------------------------------------ 1,         2,                  2 1,         3,                  3 2,         1,                  2 2,         4,                  1 3,         1,                  2 3,         2,                  2 3,         4,                  1 4,         3,                  3 

since looking specific person, can use where clause in above query:

select t1.person_id, t1.follower_person_id, t2.cnt followers t1 join (    select person_id, count(*) cnt    followers    group person_id  ) t2  on t1.follower_person_id = t2.person_id t1.person_id = 1 order t2.cnt desc limit 1 

order by limit give popular person specific person following.

output:

person_id, follower_person_id, cnt ----------------------------------- 1,         3,                  3 

note: can generalize output of second query using variables popular person each person follows (this greatest-per-group problem).