Writing SQL query for getting maximum occurrence of a value in a column

Question

I have an emp table with the records below:

INSERT into emp(EmpId,Emp name, Manager)
Values(1,A,M1)
values(2,B,M1)
values(3,C,M2)
values(4,D,M3)

How can I find the Manager having the maximum number of employees under him? In this case, output should be M1. Please help.

Answer 1

select manager, count(*) as employees from emp
  group by manager
  order by count(*) desc

Take the first record. Depending on your SQL version, you can do this with a limit statement.

Answer 2

In SQL Server...

SELECT TOP 1 Manager
FROM  ( SELECT Manager,
               COUNT(Manager) as "ManagerCount"
        FROM emp
        GROUP BY Manager
        ORDER BY "ManagerCount" DESC )

Oracle is a bit different...

SELECT Manager
FROM  ( SELECT Manager,
               COUNT(Manager) as "ManagerCount"
        FROM emp
        GROUP BY Manager
        ORDER BY "ManagerCount" DESC )
WHERE ROWNUM <= 1