I have a table of employees employee_tbl . It has the following fields:

id ,FIO_eployees, manager_id .FIO manager are not stored in a separate field. Manager's FIOs are taken from the FIO_employees field. As managers, they are also employees.

I need to bring the FIO manager, who has the most subordinates.

  • You already decide what your problem condition. - Denis
  • The employee table, FIO manager is taken from the FIO_employees field. It turns out that the full names of the manager are stored in the employee records, and not the full names of these employees. In this case, the query will help you, with grouping by FIO_eployees (error in the word), counting the grouped records and displaying the maximum. But I do not think that this is what is needed. - Samuel Loog
  • I have no three fields there. Names of managers are taken from the name field of employees. As some employees are managers. - user221704
  • @ user221704, you have already made a request with a check for sqlfiddle, which correctly works. - Denis
  • 3
    Possible duplicate question: How to make a request that displays employees and their managers (FIO employees, FIO managers)? - Kirill Stoianov

3 answers 3

Everything is very simple: 1) This is a subquery number of subordinates

 SELECT COUNT(*) FROM employee_tbl AS w WHERE w.manager_id= m.id

2) To get with the maximum number - sort descending

 ORDER BY подзапрос DESC

3) We need the most-most, i.e. one

 LIMIT 1

As a result, we obtain:

 SELECT FIO_employees FROM employee_tbl AS m ORDER BY (SELECT COUNT(*) FROM employee_tbl AS w WHERE w.manager_id= m.id) DESC LIMIT 1

Table aliases m is a manager, w is an ordinary worker

OPTION No. 2

if you still need to find several managers with the same number of subordinates

1 line - managers

2 line - number of subordinates

last line - maximum number of subordinates

 SELECT FIO_employees FROM employee_tbl AS m WHERE (SELECT COUNT(*) FROM employee_tbl AS w WHERE w.manager_id= m.id) = (SELECT COUNT(*) as maxw FROM employee_tbl AS w GROUP BY w.manager_id ORDER BY maxw DESC LIMIT 1)
  • And if we have several bosses, with the maximum number of subordinates? - Mirdin
  • In the singular question. - Mrak
  • Well, usually the condition is given, especially at the interview. But there may be cases when there are several such people. Вывести FIO менеджеров , у которых больше всего подчиненных. - It sounds a bit incorrect. - Denis
  • Added another answer - Mrak
  • one
    @Mrak try to fit everything in one answer, do not breed them. - Denis
 SELECT e_2.FIO_eployees FROM employee_tbl As e_1 LEFT OUTER JOIN employee_tbl As e_2 ON e_1.manager_id = e_2.id GROUP BY e_1.manager_id, e_2.FIO_eployees HAVING COUNT(e_1.ID) = (SELECT COUNT(id) FROM employee_tbl GROUP BY manager_id ORDER BY COUNT(id) DESC LIMIT 1)

- In the event that we do not need information about employees without a boss:

 SELECT e_2.FIO_eployees FROM employee_tbl As e_1 INNER JOIN employee_tbl As e_2 ON e_1.manager_id = e_2.id GROUP BY e_1.manager_id, e_2.FIO_eployees HAVING COUNT(e_1.ID) = (SELECT COUNT(id) FROM employee_tbl WHERE manager_id IS NOT NULL GROUP BY manager_id ORDER BY COUNT(id) DESC LIMIT 1)
  • and if null is the most? - Denis
  • @Denis, By the way, the same question for you, perhaps you should use LEFT OUTER - Mirdin
  • I did not succeed, did you understand me correctly? The column "dbo.TABLE2.FIO" is not valid in the select list, since it is not contained in the aggregate function or in the GROUP BY clause - this is the error shown - user221704
  • I have a condition on null , on sqlfiddle it works correctly. - Denis
  • @ user221704, already corrected, in GROUP BY, replace fio with FIO_eployees - Mirdin

There may be a case when the managers themselves will be, so their id_manager will be null . The limitation on id_manager IS NOT NULL comes from these considerations.

 SELECT m.fio FROM sotrudnik s JOIN sotrudnik m ON s.id_manager = m.id GROUP BY s.id_manager HAVING COUNT(s.id) = (SELECT MAX(cnt) FROM (SELECT id_manager, count(id) AS cnt FROM sotrudnik WHERE id_manager IS NOT NULL GROUP BY id_manager) t)

Sqlfiddle example