Microsoft SQL Server forum - Select option to execute the maxi number of direct subordinate

Author

Select option to execute the maxi number of direct subordinate

J.Balakumar@gmail.com

2005-12-13, 3:23 am

hai,
help me out of a select option that should makes to display only the
maximum number of subordinate to their immediate senior in an
organization.
like...
MD > 3 director > each has 2,3,4 person reporting subordinate
respectively.
i want to display one director have maximum 4 subordinates.
that 4 subordinates to be displayed.

empid name reporting to designation
1 aaa null MD
2 bbb aaa pm
3 ccc aaa pm
4 ddd aaa pm
5 eee bbb pl
6 f f f bbb pl
7 ggg ccc pl
8 hhh ccc pl
9 i i i ccc pl
10 j j j ddd SE
11 kkk ddd SE
12 l l l ddd SE
13 mmm ddd SE

I want to display the information of employee,whom they are DIRECTLY
Reporting to,who is having maximum number of direct subordianate.i want
to display of SE(designation) to be get display.
help me out of a select statement to view while execution.

Erland Sommarskog

2005-12-13, 8:25 pm

(J.Balakumar@gmail.com) writes:
> help me out of a select option that should makes to display only the
> maximum number of subordinate to their immediate senior in an
> organization.
> like...
> MD > 3 director > each has 2,3,4 person reporting subordinate
> respectively.
> i want to display one director have maximum 4 subordinates.
> that 4 subordinates to be displayed.
>
> empid name reporting to designation
> 1 aaa null MD
> 2 bbb aaa pm
> 3 ccc aaa pm
> 4 ddd aaa pm
> 5 eee bbb pl
> 6 f f f bbb pl
> 7 ggg ccc pl
> 8 hhh ccc pl
> 9 i i i ccc pl
> 10 j j j ddd SE
> 11 kkk ddd SE
> 12 l l l ddd SE
> 13 mmm ddd SE
>
>
> I want to display the information of employee,whom they are DIRECTLY
> Reporting to,who is having maximum number of direct subordianate.i want
> to display of SE(designation) to be get display.
> help me out of a select statement to view while execution.

So there is a standard recommendation for this sort of post, and
that is that you include:

1) CREATE TABLE statment for your table(s).
2) INSERT statments with sample data.
3) The desired result given the sample.

It makes it possible to copy and paste into Query Analyzer to develop
a tested solution. It also serves to sort out any ambiguities in
your narrative. I have read your post a couple of times, but I don't
really understand what you want. Since I want to give you something
that works, I need to ask you for more detailed input.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Doug

2005-12-14, 1:23 pm

I'm rusty. I trust the peanut gallery will assist me with syntax
issues.

select employee.name, boss.name, count(employee.empid) from people as
employee
left outer join people as boss on employee.empid = boss.reportingto
where designation = 'PM'
group by employee.name, boss.name

you want the left outerjoin because sooner or later you will run into
the president of the company, who has no boss.

Sponsored Links