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.
query to find list of max. members?
Moderator: General Moderators
Re: query to find list of max. members?
You need to a LEFT JOIN and GROUP BY.
If you still have difficulties, post your query here.
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
Re: query to find list of max. members?
This will give you the group_id's with the number of members, sorted:
Given your example, this would result in:
Can you manage to get the group names corresponding with the group_id's yourself?
Code: Select all
SELECT group_id,COUNT(member_id) AS n FROM group_member GROUP BY group_id ORDER BY n DESCCode: Select all
3 11
2 8
1 6
4 3Re: query to find list of max. members?
thanks it works
Thank you very much
Thank you very much