0

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!

3 Answers3

5
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.

Jamie Ide
  • 48,427
  • 16
  • 81
  • 117
  • +1 for enlightening me to "with ties". I'm ashamed I didn't know that already. – Dusty Aug 06 '09 at 14:34
  • I think you can't use an order by in a sub-select. It goes againt the relational model. You should move it to the outer select. – neves Aug 07 '09 at 00:03
  • @neves: I don't understand your comment. The list has to be ordered before you can select the top 5 the ordering must be done in a subquery because it requires an aggregate. Also, it's not possible to break the relational model with a select. Fight it, yes, but not break it. :-) – Jamie Ide Aug 07 '09 at 00:21
2
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 :)

jr3
  • 915
  • 3
  • 14
  • 28
0

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;
Matt McCormick
  • 13,041
  • 22
  • 75
  • 83