CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Ryan
Ryan

Reputation:

Top 5 with most friends

Hi I'm new to SQL and I'm trying to figure out how I'm going to get the top 5 "bands" with most friends (userId) and this is what i have; a usertbl with userId as PK then a bandsTbl with bandId as PK then I have a table bandfriends with FK userId and bandId. 

bandfriends
userid | bandId
---------------
 1     | 1
 1     | 2
 1     | 3

Thanks!

Upvotes: 0

Views: 536

Answers (3)

jr3
jr3

Reputation: 915

select top 5 b.b_name, count(friends) as numOfFriends 
from bands b inner join link l on b.b_id = l.bands inner join
friends f on f.f_id = l.friends 
group by b.b_name 
order by numOfFriends desc

If you have friends table, bands table and a link table, works for me :)

Upvotes: 2

Jamie Ide
Jamie Ide

Reputation: 49271

SELECT TOP 5 bandId, fanCount
FROM 
    (SELECT bandId, COUNT(*) as fanCount
     FROM bandfriends
     GROUP BY bandId
     ORDER BY COUNT(*) DESC)

You can also optionally specify WITH TIES in the select statement. See this and this.

Upvotes: 5

Matt McCormick
Matt McCormick

Reputation: 13200

Read up on COUNT and GROUP BY at mysql.org

You'll want something like this (I haven't tested it):

SELECT bandId, COUNT(*) as fans FROM bandfriends
ORDER BY fans DESC
GROUP BY bandId
LIMIT 5;

Upvotes: 0

Related Questions