yogeshgirnar yogeshgirnar - 1 month ago 7
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)
)x
)y
where rnk=1


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

Answer

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;

Notes:

  • 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.