Sql query for this?

Question

person_id | manager_id | name |
          |            |      |
-------------------------------

Query to find name of manager who supervises maximum number of employees?

Added: This is the only table. Yes self-referencing. DB is mysql. Recursive queries will also do.

Answer 1

This query returns the manager_id and manager_name of the manager with the maximal number of employees.

The trick is in the HAVING clause, which allows aggregates and counts over multiple rows.

SELECT manager_id,name, count(*) 
    FROM table 
    GROUP BY manager_id, name
    HAVING max(count(*));

You can read more in the short but informative w3schools.com HAVING clause tutorial.

If the manager_id references a person id in the same table, Svinto's answer might be more suitable.

Answer 2

SELECT name 
FROM table 
WHERE person_id = (
    SELECT manager_id 
    FROM table 
    GROUP BY manager_id 
    HAVING max(count(*)))

Answer 3

Assuming manager_id have a reference to person_id and name of table: table_name

SELECT name FROM (
  SELECT manager_id
  FROM table_name
  GROUP BY manager_id
  ORDER BY COUNT(*) DESC
  LIMIT 1
) t
INNER JOIN table_name ON t.manager_id = table_name.person_id

edit: Removed HAVING MAX COUNT, added ORDER BY COUNT DESC LIMIT 1 in subquery

Answer 4

It's not entirely clear to me what you want, so if this isn't what you want please clarify your question.

This query returns just one of the managers if there is a tie:

SELECT T2.name FROM (
    SELECT manager_id
    FROM table1
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
    ORDER BY count(*) DESC
    LIMIT 1
) AS T1
JOIN table1 AS T2
ON T1.manager_id = T2.person_id

Result of query:

Bar

Here's a query that fetches all managers with the tied maximum count in the case that there is a tie:

SELECT name FROM (
    SELECT manager_id, COUNT(*) AS C
    FROM person
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id) AS Counts
JOIN (
    SELECT COUNT(*) AS C
    FROM person
    WHERE manager_id IS NOT NULL
    GROUP BY manager_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
) AS MaxCount
ON Counts.C = MaxCount.C
JOIN person
ON Counts.manager_id = person.person_id

Result of the second query:

Foo
Bar

Here's my test data:

CREATE TABLE Table1 (person_id int NOT NULL, manager_id nvarchar(100) NULL, name nvarchar(100) NOT NULL);
INSERT INTO Table1 (person_id, manager_id, name) VALUES
(1, NULL, 'Foo'),
(2, '1', 'Bar'),
(3, '1', 'Baz'),
(4, '2', 'Qux'),
(5, '2', 'Quux'),
(6, '3', 'Corge');