Question: I have three entities: department, employee, and report. A department has many employees, each of whom has many reports. I want to select the one employee in each department who has the most reports. I have no idea how to even start this query. [This question](https://stackoverflow.com/questions/1563148/one-to-many-query-selecting-all-parents-and-single-top-child-for-each-parent) seems very similar, but I can't figure out how to manipulate those answers for what I want.

I have full access to the entire system, so I can make any changes necessary. In the event of a tie, it's safe to arbitrarily pick one of the results.

Department:

```
 ID | Name
----|------
  1 | DeptA
  2 | DeptB
  3 | DeptC
  4 | DeptD

```

Employee:

```
 ID | Name | DeptID
----|------|--------
  1 | Joe  | 1
  2 | John | 1
  3 | Emma | 2
  4 | Jack | 3
  5 | Sven | 3
  6 | Axel | 4
  7 | Brad | 4
  8 | Jane | 4

```

Report:

```
 ID | EmployeeID
----|------------
  1 | 1
  2 | 2
  3 | 3
  4 | 5
  5 | 6
  6 | 6
  7 | 8

```

Desired result (assuming I queried names only):

```
Joe OR John (either is acceptable)
Emma
Sven
Axel

```

Answer: 
How to start this query? Well, get the information about each employee, the department, and the number of reports:

```
select e.name, e.deptid, count(*) as numreports
from employee e join
     reports r
     on e.id = r.employeeid
group by e.name, e.deptid;

```

Now you just want the largest count in each department. I would suggest `row_number()` or `rank()` depending on how you want to handle ties:

```
select er.*
from (select e.name, e.deptid, count(*) as numreports,
             row_number() over (partition by e.deptid order by count(*) desc) as seqnum
      from employee e join
           reports r
           on e.id = r.employeeid
      group by e.name, e.deptid
     ) er
where seqnum = 1;

```

If you want the department name instead of number, you can join that in as well.
Please try the below code:- 

```
SELECT D.NAME
FROM (
    SELECT C.NAME, RANK() OVER (
            PARTITION BY C.DEPTID ORDER BY C.COUNTS DESC
            ) RNK
    FROM (
        SELECT EMPID, NAME, COUNT(EMPID) AS COUNTS, DEPTID
        FROM DBO.REPORT AS A
        JOIN DBO.EMPLO AS B ON A.EMPID = B.ID
        GROUP BY EMPID, NAME, DEPTID
        ) AS C
    ) AS D
WHERE D.RNK = 1

```