Select option to execute the maxi number of direct subordinate

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.

(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

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.