yogeshgirnar yogeshgirnar - 1 year ago 67
SQL Question

Find the month in which maximum number of employees hired

I have a situation where I need to find the month in which maximum number of employees hired.

Here is my Employee table:

enter image description here

Although I have a solution for this:

select MM
from (
select *, dense_RANK() OVER(order by cnt desc) as rnk
from (
select month(doj) as MM,count(month(doj)) as CNT
from employee
group by month(doj)
where rnk=1

But I am not satisfied with what i have implemented and want the most feasible solution for it.

Answer Source

I think the simplest way is:

select top 1 year(doj), month(doj), count(*)
from employee
group by year(doj), month(doj)
order by count(*) desc;


  • This interprets "month" as being "year/month". If you really do only want the month, then remove year() from both the select and group by.
  • This returns one row. If you want multiple rows when there are ties, then use select top (1) with ties.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download