query to find list of max. members?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kalpesh
Forum Commoner
Posts: 54
Joined: Sun Sep 21, 2008 5:04 am

query to find list of max. members?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: query to find list of max. members?

Post by VladSun »

You need to a LEFT JOIN and GROUP BY.
If you still have difficulties, post your query here.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: query to find list of max. members?

Post 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?
kalpesh
Forum Commoner
Posts: 54
Joined: Sun Sep 21, 2008 5:04 am

Re: query to find list of max. members?

Post by kalpesh »

thanks it works

Thank you very much
Post Reply