The most children

[SimonTheSponge]

Having a bit of mind going blank day

I have a parent table

Member
Membership_no (char(10)) PK
First_name (char(30))
Surname (char(30))

and a child table

Role_no (char(10)) PK
Membership_no (char(10)) FK
...and various other fields

I want a query to return the membership_no, first_name, surname and the number of roles they have for the member with the most roles and . This is running SQL 6.5

Cheers

 

[manjarikosaraju]

select A.membership_no, A.First_Name, A.SurName, B.Cnt as RoleCnt
from #Member A join
(
select top 1 membership_no,count(distinct role_no) as Cnt from #Role group by membership_no order by Cnt desc
) B
on A.membership_no = B.membership_no

-Manjari

 

[Juice05]

Select Membership_no, First_name, Surname, Count(Role_no)

From Member

Inner Join Table2 On Table2.Membership_no = Member.Membership_no

Group By Membership_no, First_name, Surname

 

[Juice05]

Sorry, didn't read the entire post

 

[SimonTheSponge]

manjarikosaraju, unfortunately Top x is not available in SQL Server 6.5 which is where I'm having problems at the moment ;-)

 

[JamesLean]

Is this what you want?

SET ROWCOUNT 1

SELECT m.memberid, m.firstname, m.surname, COUNT(*) AS roles
FROM member m JOIN role r ON m.memberid = r.memberid
GROUP BY m.memberid, m.firstname, m.surname
ORDER BY COUNT(*) DESC

SET ROWCOUNT 0

--James

 

[manjarikosaraju]

I am not very familiar with SQL Server 6.5 syntax

Try this:

Declare @MaxCnt int
select @MaxCnt = max(x.Cnt)
from (select membership_no, count(distinct role_no) as Cnt from #Role group by membership_no) x

select A.membership_no, A.First_Name, A.SurName, @MaxCnt as RoleCnt
from #Member A join #Role B on A.membership_no = B.membership_no
group by A.membership_no,A.First_Name, A.SurName
having count(distinct role_no) = @MaxCnt

Hope this helps.

-Manjari

 

[SimonTheSponge]

LOL, I'm almost tempted to give you a star for a great bit of lateral thinking James. Unfortunately it is possible for there to be more than one member sharing the most number of roles in which case I'd like both/all reported ;-)

Thanks Manjarikosaraju this has given me a pointer

Could you not achieve it with the single statement

select A.membership_no, A.First_Name, A.SurName, count(*) as RoleCnt
from #Member A join #Role B on A.membership_no = B.membership_no
group by A.membership_no,A.First_Name, A.SurName
having count(*) = (select max(x.Cnt)
from (select membership_no, count(distinct role_no) as Cnt from #Role group by membership_no) x)



Cheers