Posted by Erland Sommarskog on 10/06/43 11:34

(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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]