Writing an SQL query to get the maximum value of a value in a column

Question

I have an emp table with the entries 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 a Manager with the maximum number of employees under it? In this case, the 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 version of SQL, you can do this with the limit statement.

Answer 2

 SELECT Manager, count(Manager) AS 'Num of Emps' FROM emp GROUP BY Manager ORDER BY 'Num of Emps' DESC 

The first entry will be a manager with most employees. Also, based on the db provider, you can limit the result set to 1, so that you only get the highest record. Here is an example of using an SQL server:

  SELECT TOP 1 Manager, count(Manager) AS 'Num of Emps' FROM emp GROUP BY Manager ORDER BY 'Num of Emps' DESC 

Answer 3

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 little different ...

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

Answer 4

In Postgresql, create a Test schema:

 create table Test.Employee (Emp_id numeric, manager_id numeric, Manager_name varchar(20)); insert into Test.Employee(emp_id, manager_id, manager_name ) values(1, 3, 'A'), (2, 3, 'A'), (3, 3, 'A'), (4, 3, 'A'), (5, 11, 'B'), (6, 12, 'C'), (7, 11, 'B'); select manager_name from (select count(manager_id) as mcount, manager_name from test.employee group by manager_name order by mcount DESC) AS TEMP limit 1 

Answer 5

If you need a row from the emp table, use the following command:

 select * from emp where empid in (select manager from (select manager, count(*) from emp group by 1 having count(*) = (select max(count) from (select manager, count(*) as count from emp group by 1) x) ) y ); 

This will also return a few lines in case there is a link for most employees.

Answer 6

 SELECT count(e.last_name) count, d.last_name FROM employees e LEFT OUTER JOIN employees d ON e.manager_id = d.employee_id GROUP BY d.last_name ORDER BY count DESC;