CODEWITHSUNDEEP
mysqlsql
AJ.
AJ.

Reputation: 2569

What is the SQL query for finding the name of manager who supervises maximum number of employees?

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.

Upvotes: 2

Views: 10479

Answers (4)

Alex LE
Alex LE

Reputation: 20572

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

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838666

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');

Upvotes: 2

Svante Svenson
Svante Svenson

Reputation: 12478

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

Upvotes: 4

Adam Matan
Adam Matan

Reputation: 136341

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.

Upvotes: 5

Related Questions