Need help SQL querry

MichaelTJ · November 17th, 2003, 08:23 PM

I have a table called GroupMembers
This has 4 col's named GroupName, GroupLevel, PhoneNr and GroupType
Info like this:
**************TABLE GroupMembers Content********
Whole Country -- Level1 -- 12332112 -- 1
StreetCar ------ Level2 -- 90000000 -- 1
BMW ---------- Level3 -- 90001800 -- 1
Mercedes ----- Level3 -- 90001800 -- 1
Oldsmobile ----- Level3 -- 90000000 -- 4
Raceing -- Level4 -- 90000000 -- 1
Raceing -- Level4 -- 90080000 -- 1
************************************************

I need to make a querry that gives me the top 10 groups with most members in it. And I can't figure out this puzzle.. If I use subquerries then I can only get one hit, with count and such.

So if anybody with some clue would spare a few moments to hel I will apreciate it!

Thanks in advance folks!

------------------------
All help is Good help!
Regards
Michael

planoie · November 17th, 2003, 10:07 PM

I'm confused by your table structure. Is this the table of members of a group or the table of the groups?

Peter
------------------------------------------------------
Work smarter, not harder.

MichaelTJ · November 17th, 2003, 10:23 PM

This is the table with group members. I have one table containing members info (MEMBERS) and on that contains groups info (GROUPS). This third is to keep track of what members are in what group.

When a member joins a group, a row is inserted into GroupMembers containig data from both GROUPS and MEMBERS.
Does that help?

------------------------
All help is Good help!
Regards
Michael

planoie · November 17th, 2003, 10:36 PM

So this table is a join table. That throws me cause it's got a large amount of descriptive data for a join table. Usually joins table (at least all the ones I've worked with) contain just two or three columns, and they are usually just key columns like integer fields that are keyed back to the main tables. I'm curious to see your other table layouts.

My wrambling about table layout aside, here's an example query that should get you in the right direction...

SELECT GroupName, COUNT(GroupName) AS MemberCount
FROM GroupMembers
GROUP BY GroupName
ORDER BY MemberCount DESC

You can order by the alias of a column so in this case we'll order by the aggregation result "MemberCount". In MS-SQL server you can also select a limited set of result rows:

SELECT TOP 10 GroupName, COUNT(GroupName) AS MemberCount
...

Peter
------------------------------------------------------
Work smarter, not harder.

MichaelTJ · November 17th, 2003, 11:28 PM

Worked like a charm.... as always!

If you were around in Norway I'd buy you a beer..

btw. If you are interrested in some table layout give me a word and I'll post it for you. (An easy way to get the layout in text form would be nice to have then, if you got one he he he)

Thanx again for your help Peter

------------------------
All help is Good help!
Regards
Michael

planoie · November 18th, 2003, 01:23 PM

FYI: In SQL query analyzer you can type "sp_help <tablename>" and with your results showing as text, you can get the table details.

Glad you got it working.

Peter
------------------------------------------------------
Work smarter, not harder.

MichaelTJ · November 19th, 2003, 11:37 PM

Thanx for another helpfull tip Peter... I'm new (getting better) at ASP.NET. But still quite hopeless in SQL. **Thinking** Wonder how I actually make things work he hehe.
My tables as such a mess, I need to clean up before I post'em....
Cant show people how lost I really am!

Thanx agian, and don't worry. I'll ask again when stuck on something new..

------------------------
All help is Good help!
Regards
Michael