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

user_875619 0
Viewed 58650

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.

sql
8 Answers
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;
user_29402160

Tested With SQL Server 2017

Select TOP 1 City, Count(City) AS 'MAX_COUNT' FROM Customer Group By City Order By 'MAX_COUNT' DESC;

Hope this simple query will help many one.

user_39023390

If you are using Oracle Database, you can simply use stats_mode function this will return single value with highest occurrences.

select stats_mode(manager) from emp;

This is very easy to use function instead of writing multiple lines of sql query.

user_89805400
Related