Thread: Retrieve data from emp table

Hi All,

Can anybody help me in solving this simple query.
I have one emp table where empname and hiredate are few columns.
I want to retrieve in which year most of the employess are joined.
So in my table in the year 1981 more employess are joined.
Can anybody help how to extract this year.

Regards

Sampurna

select to_char(hiredate,YYYY),max(count(to_char(hiredate,YYYY)))
from emp
groupby to_char(hiredate,YYYY),;

This will retrieves the year in which maximum employees joined and how many joined in that year.

Hi,

Try this:-

select hire_dt,emp_name from (select rownum rw, emp_name,hire_dt from(select * from emp order by hire_dt desc)) where rw =&n;

here u will give value of n as position u want.eg.
first highest year have n=1,
sec will have n=2,
third n=3 and so on...........

select hire_dt,emp_name from (select rownum rw, emp_name,hire_dt from(select * from emp order by hire_dt desc)) where rw =1;

Thanks
Reeta

Hi
try this query

select to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy') having count(to_char(hiredate,'yyyy'))= (select max(count(*)) from emp group by to_char(hiredate,'yy'));

Regards
Sasikumar