Can anyone try how to solve the query for this table structure?

Employee 开发者_如何学编程table structure

first_name varchar2(100)
last_name  varchar2(100)
hire_date  date

show all the employee who hired on the day of the week on which highest number of employees were hired?

select first_name, last_name from employee 
where 
DAYNAME(hired_date) = (
select top 1 dow 
from (
select COUNT(first_name) as cnt, DAYNAME(hire_date) as dow 
from employee
group by (dow)
order by cnt desc
)
)

This is with mysql, but I am certain there are similar functions in db2, oracle, ms sql server, etc.

Try this

   select top 1 * from( 
    select count(first_name) as countemp from employee
    where hire_date between DATEADD(day,-7,getdate())
                        and  getdate()
 group by(hire_date)   ) d order by countemp desc