Page 1 of 1

query to find list of max. members?

Posted: Fri Nov 06, 2009 5:02 am
by kalpesh
Hi,
I have 3 tables which is as follow
1)User with column names
User_Id
User_Name
2)group with column names
group_id
group_name
creator(which is foreign key to User_Id )
3)group_member with column names
member_id
group_id
member_name


I want to type query to find list of groups having maximum no of groupmember.
means
if group1 has 6 members,group2 has 8 members,group3 has 11 members and group4 has 3 members
then result will be like this:
Groupname membercount
group3 11
group2 8
group1 6
group4 3

Please help me
Thanks in advance.

Re: query to find list of max. members?

Posted: Fri Nov 06, 2009 5:11 am
by VladSun
You need to a LEFT JOIN and GROUP BY.
If you still have difficulties, post your query here.

Re: query to find list of max. members?

Posted: Fri Nov 06, 2009 5:38 am
by Apollo
This will give you the group_id's with the number of members, sorted:

Code: Select all

SELECT group_id,COUNT(member_id) AS n FROM group_member GROUP BY group_id ORDER BY n DESC
Given your example, this would result in:

Code: Select all

3 11
2 8
1 6
4 3
Can you manage to get the group names corresponding with the group_id's yourself?

Re: query to find list of max. members?

Posted: Fri Nov 06, 2009 5:54 am
by kalpesh
thanks it works

Thank you very much